|
予め配列のサイズを決めてWorksheetFunction.Transposeを使わなければ
Sub Test2()
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets(1)
Set ws2 = wb.Worksheets(2)
Dim a() As Variant
Dim n As Long, i As Long, j As Long
'A例に定数が含まれるセルの数
n = ws1.Columns(1).SpecialCells(xlCellTypeConstants).Count
ReDim a(1 To n, 0) '二次元配列
For i = 1 To ws1.Cells(Rows.Count, 1).Row
If ws1.Cells(i, 1) <> "" Then
j = j + 1
a(j, 0) = ws1.Cells(i, 1).Value
End If
Next
ws2.Range("A1").Resize(n).Value = a
End Sub
|
|