Solved: Copy Next Column of Data

[mphill]

Issue: Do to formatting problems I am not able to copy and paste a range of cells in to our design software more than one column wide. I am able to copy a column and paste it. I would like to start with cell D7:D and go to the last row of data in that column, then go to E7:E...until an empty cell is found in the row\column.

My first section of commented out code works but ends at the first column. I've tried to tweak that around to get the next column(s) but after numerous attempts it is still not clear to me how to get that accomplished. :banghead: Thanks for any and all assistance.

''Populate Listbox with a single column of data "WORKS"
' Do While exlSheetInput.Cells(EmptyRow, 4) <> ""
' If exlSheetInput.Cells(EmptyRow, 4) = "" Then Exit Do
' EmptyRow = EmptyRow + 1
' Loop
'
' UserForm1.ListBox1.List = exlSheetInput.Range("D7:D" & EmptyRow - 1).Value
' With Me.ListBox1
' For i = 0 To .ListCount - 1
' Debug.Print .List(i) & Chr(13)
' Next i
' End With

'Populate Listbox with consecutive columns of data until column and row cell is empty
'Start with cell D7:D, typically only 7 rows of data but the number of columns are either
'11, 22, or 33.
Do While exlSheetInput.Cells(EmptyRow, 4) & exlSheetInput.Cells(7, EmptyCol) <> ""
If exlSheetInput.Cells(EmptyRow, 4) & exlSheetInput.Cells(7, EmptyCol) = "" Then Exit Do
EmptyRow = EmptyRow + 1
EmptyCol = EmptyCol + 1
Loop
UserForm1.ListBox1.List = exlSheetInput.Range("D7:D" & EmptyRow - 1).Value
With Me.ListBox1
For Each rngCol In exlSheetInput.UsedRange.Columns
exlSheetInput.Range(rngCol.Cells(7, 4), rngCol.Cells(exlSheetInput.Rows.Count, 1).End(xlUp)).Text
For i = 0 To .ListCount - 1
Debug.Print .List(i) & Chr(13)
Next i
Next
End With

[p45cal]

What value does the ColumnCount property of your listbox have. It defaults to 1 but yours should be more.

[mphill]

The columncount is 1 but the first column is the only one that prints, understandably. If the second and subsquent columns read print below in the listbox or immediate window, instead of side by side that will suffice. This will be going in to another program and the listbox was for testing to see if the other columns would be read. I will change the number though and see.

[mphill]

I have it working now with the following.
Dim i As Integer
Dim j As Integer
With mySheet '("Input Sheet")
i = 4
Do Until .Cells(7, i).Value = ""
For j = 7 To 14
Debug.Print .Cells(j, i)
' Debug.Print .Cells(j, i + 1)
Next j
i = i + 1
Loop
End With

[mdmackillop]

Use Long rather than Integer. Spreadsheets have more rows and columns than the Integer size limit. Integers are converted to Long in any case, so no memory advantage.

[mphill]

Thank you for the helpful additional information.