Another using an array..
Private Sub CommandButton1_Click()
Dim x, z, i As Long, ii As Long, cnt As Long
With Range("A1").CurrentRegion
ReDim z(1 To 1, 1 To .Rows.Count * .Columns.Count)
For i = 1 To Range("IV1").End(xlToLeft).Column
For ii = 1 To .Rows.Count
z(1, ii + cnt) = Cells(ii, i) & " " & i
Next ii
cnt = cnt + ii - 1
Next i
End With
With Sheets("Sheet2")
.Cells(1, 1).Resize(UBound(z, 2), 1).Value = Application.Transpose(z)
.Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, Space:=True
End With
End Sub
Bookmarks