VBAで A列のデータを５行ずつ、改列して並べたい

A列のデータを５行ずつ、改列（という言い方はおかしいかもしれませんが）して並べたいです。
よろしくお願いいたします。

No.4 回答者： WindFaller 回答日時： 2019/08/17 01:57

こちらも試してみてください。

メモリ不足というのは、Excel 2013以降の問題なんでしょうね。
何にか良くわからないけれども、こちらもメモリ喰いが発生しているようです。
横につなげるというのは、なにか問題があるのかもしれませんね。

'//
Sub ConvertNum2Col()
　Dim x As Variant
　Dim i As Long, j As Long, k As Long
　'セッティング
　Dim ar(1 To 5, 1 To 5)
　Dim nTotal As Long: nTotal = 25
　Dim sh1 As Worksheet, sh2 As Worksheet
　Dim limitCol As Long
　Dim col As Long
　limitCol = Columns.Count

　Set sh1 = Worksheets("Sheet1")
　Set sh2 = Worksheets("Sheet2")

　sh2.UsedRange.ClearContents
　With sh1
　　x = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Value
　End With
　k = 1
　Application.ScreenUpdating = False
　Do
　　For i = 1 To 5
　　　For j = 1 To 5
　　　　DoEvents
　　　　ar(j, i) = x(k, 1)
　　　　col = (Int(k / nTotal) - 1) * 5
　　　　If limitCol <= (col + 5) Then
　　　　　sh2.Cells(1, (Int(k / nTotal) - 1) * 5 + 1).Resize(5, limitCol - col).Value = ar
　　　　　Exit Sub
　　　　End If
　　　　If k Mod nTotal = 0 Then
　　　　　sh2.Cells(1, (Int(k / nTotal) - 1) * 5 + 1).Resize(5, 5).Value = ar
　　　　　Erase ar()
　　　　End If
　　　　k = k + 1
　　　Next j
　　Next i
　Loop Until k > UBound(x)
　Application.ScreenUpdating = True
End Sub

No.3 回答者： tom04 回答日時： 2019/08/15 22:17

続けてお邪魔します。

＞この処理をしたいデータだとメモリが不足していますとなり

Sheet1のA列データは何行目まであるのでしょうか？
Excelの最終列はXFD列の16384列なので、その5倍の81920行までのデータ数であれば
少し時間を要しても前回のコードで大丈夫だと思います。
少し時間短縮をしたい場合は

最初に
＞Application.ScreenUpdating = False
と
最後に
＞Application.ScreenUpdating = True
を追加すればほとんど時間は要しないとおもいます。

※　もし、Sheet1のデータ数が81921行以上ある場合は
Sheet2を2段で表示する必要があると思います。

※　実際、そんなにデータが多いとSheet2の画面をスクロールするのも大変でしょうけど・・・m(_ _)m

No.2 回答者： tom04 回答日時： 2019/08/15 21:05

No.1です。

投稿後気づきました。
VBAでの方法をご希望だったのですね。
No.1で回答したように元データはSheet1のA1セル以降にあり、Sheet2のA列以降5行ずつ表示するとします。

Sub Sample1()
　Dim i As Long, cnt As Long
　Dim wS As Worksheet
　　Set wS = Worksheets("Sheet2")
　　　wS.Cells.ClearContents
　　　　With Worksheets("Sheet1")
　　　　　For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row Step 5
　　　　　　cnt = cnt + 1
　　　　　　　wS.Cells(1, cnt).Resize(5).Value = .Cells(i, "A").Resize(5).Value
　　　　　Next i
　　　　End With
End Sub

こんな感じでもいけると思います。m(_ _)m

この回答へのお礼

ありがとうございます。
早速やってみました。

少ないデータだとうまく動きました。
私がこの処理をしたいデータだとメモリが不足していますとなり
エラーが出てしまいました。ちょっと頑張ってみます。
助かりました。

お礼日時：2019/08/15 21:38

No.1 回答者： tom04 回答日時： 2019/08/15 21:00

こんばんは！

色々やり方はあると思いますが、一例です。
元データはSheet1のA1セルからあり、Sheet2のA1セル以降に表示するとします。

Sheet2のA1セルに
=IF(COUNTA(Sheet1!$A:$A)<ROW(A1)+(COLUMN(A1)-1)*5,"",OFFSET(Sheet1!$A$1,(COLUMN(A1)-1)*5+ROW(A1)-1,,1))

という数式を入れ下へ5行分、そのまま右へフィル＆コピー！

こんな感じではどうでしょうか？

※　Sheet1のA列には途中に空白セルはない！という前提です。m(_ _)m