vba counter loop for columns

Question

The aim of my code is to copy each column and paste each below the other in column A.

I am trying to use a counter loop to loop through columns one at a time, but I can't work out how to reference each column to get all of the used cells in the column. Pretty straightforward for rows, but for columns do I either need to set a variable to string and change it to a letters counter, or can I refer to a column using a number in the range format?

Below code doesn't work when I try and select the entire column.

Sub testing()

Dim i As Integer
Dim lastrow As Integer
Const g As Byte = 2

lastrow = Range("b" & Rows.Count).End(xlUp).Row

For i = g To Cells(1, Columns.Count).End(xlLeft).Column

*Cells(1, i).EntireColumn.End(xlUp).Copy*
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

Next i

End Sub

Can this [answer](https://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column/2980478#2980478) help? — Alex P, Apr 30 '20 at 12:22

Absinthe · Accepted Answer · 2020-04-30T12:48:55.723

Cells(1, i).EntireColumn.End(xlUp).Copy will only copy one cell as the End method only selects a single cell. Try using the Range object to specify the starting (top left) cell of the range and the end (bottom right) cell:

For i = g To Cells(1, Columns.Count).End(xlLeft).Column
   lastRow = cells(1000000, i).end(xlup).row
   Range(Cells(1, i), Cells(lastRow, i)).copy
   Range("a1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Next i

Edit: it's safer (and a good habit) to specify the object we're working with

dim ws as worksheet
Set ws = Sheets("The name of the sheet I'm working on")

For i = g To ws.Cells(1, ws.Columns.Count).End(xlLeft).Column
   lastRow = ws.cells(1000000, i).end(xlup).row
   ws.Range(ws.Cells(1, i), ws.Cells(lastRow, i)).copy
   ws.Range("a1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Next i

As noted in the comments it can be more character-efficient to use a With block

dim ws as worksheet  
Set ws = Sheets("The name of the sheet I'm working on")

With ws 
   For i = g To .Cells(1, ws.Columns.Count).End(xlLeft).Column
      lastRow = .cells(1000000, i).end(xlup).row
      .Range(ws.Cells(1, i), .Cells(lastRow, i)).copy
      .Range("a1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
   Next i
End With

i would also assist user by specifying to tie the objects to their parents (with a `With` block). — Scott Holtzman, Apr 30 '20 at 12:33
@ScottHoltzman Yup, sure. I'd posit a with block is sometimes a matter of taste and sometimes a timesaver. Good to present all the options. — Absinthe, Apr 30 '20 at 12:50

vba counter loop for columns

1 Answers1