转置不同数量的行，直到出现不同数量的空白VBA

Sub TransposeRows()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim rowCount As Integer
    Dim colCount As Integer
    
    ' 设置源数据范围
    Set sourceRange = Range("A1:A10")
    
    ' 获取源数据行数和列数
    rowCount = sourceRange.Rows.Count
    colCount = sourceRange.Columns.Count
    
    ' 设置目标数据范围
    Set targetRange = Range("B1").Resize(colCount, rowCount)
    
    ' 循环遍历源数据并转置到目标数据范围
    For i = 1 To rowCount
        For j = 1 To colCount
            targetRange.Cells(j, i).Value = sourceRange.Cells(i, j).Value
        Next j
    Next i
End Sub