ExcelVBAマクロで貼り付け先の配列化について

質問

ExcelVBAマクロについて確認させてください。

プログラム高速化のために２番目のワークシート貼り付け先範囲を配列化しました。
１番目のワークシートの文字をtrim関数によって前後の空白を除去したうえで２番目のワークシートに貼り付けようとしていますが、空白が除去できていない状況です。

何か原因なのでしょうかm(__)m

Sub test()

Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long
Dim a As String

Dim Table As Variant '配列化のため

'最大列取得
x = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
'最大行取得
y = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Table = Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(y, x)) '貼り付け先範囲を配列化

For x2 = 1 To x '最大列

For y2 = 1 To y '最大行

a = Worksheets(1).Cells(y2, x2).Value

Table(y2, x2) = Trim(a) 'ワークシート１の値から前後空白を取り除いてワークシート２に貼り付け

Next

Next

End Sub

回答No.3

dell_OK · ベストアンサー

回答No.1です。
投稿後に気づいたのですが（すでに回答No.2さんが言われてました）
配列元はシート１の方がいいと思います。
こうなるのかな。
'ワークシート１を配列化
Table = Worksheets(1).Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(y, x))
For x2 = 1 To x '最大列
For y2 = 1 To y '最大行
'配列の値から前後空白を取り除く
Table(y2, x2) = Trim(Table(y2, x2))
Next
Next
'ワークシート２に貼り付け
Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(y, x)) = Table

お礼

なるほど。分かりました！配列の仕組みを勘違いしていたようです。。今後の参考にさせていただきます。

回答No.4

imogasi

質問で質問者は何がしたいのか、なぜそうするのか、理解できてない、かもしれない。
私の狙いは、ForNextなどのシコシコ繰り返しコードをなくすこと。
ただそれが実現できない部分が残ったが。
見当はずれなら、ご免。
標準モジュールに
Sub test01()
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1").CurrentRegion.Select
myarray = Selection.Value
h = Selection.Rows.Count: MsgBox h
w = Selection.Columns.Count: MsgBox w
Worksheets("Sheet2").Range("b5").Resize(h, w) = myarray
For Each cl In Worksheets("Sheet2").Range("b5").Resize(h, w)
cl.Value = Trim(cl.Value)
Next
End Sub
ーーー
例データ
Sheet1　A1:C４
a b c
1 dd fg
3 gg xj
5 gg h
結果
Sheet2にB5:D8
a b c
1 dd fg
3 gg xj
5 gg h
ーーー
Worksheets("Sheet2").Range("b5").Resize(h, w) =Trim(myarray)
ができれば、もっと良いが、この書き方できないよう。

お礼

for next文も出来るだけ減らすのも手ですね。ありがとうございました！

回答No.2

kkkkkm

最後に
Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(y, x)) = Table
がなければ配列をWorksheets(2)に書き戻せていないのではないでしょうか。書き戻したらデータから空白が除去できていると思います。

高速化でしたらWorksheets(1)のデータを配列に読み読んでそのデータをTrimしてWorksheets(2)に上記の方法で書き込む方がいいのではないでしょうか。

お礼

配列に入っていたままになっていたんですね。おかげさまでイメージが分かりました！ありがとうございます^ ^

回答No.1

dell_OK

空白除去はできていますが、貼り付けができていないようです。
最後に以下のようにしてシートに戻す必要があるのではないでしょうか。
Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(y, x)) = Table

お礼

ちゃんと戻せてなかったようですね。参考になりました。色々とありがとうございますm(__)m