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 help?

Alex P
– Alex P

2020-04-30 12:22:57 +00:00
Commented Apr 30, 2020 at 12:22 — Alex P
– Alex P, Commented Apr 30, 2020 at 12:22

Absinthe · Accepted Answer · 2020-04-30 12:48:55Z

1

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

edited Apr 30, 2020 at 12:48

answered Apr 30, 2020 at 12:29

Absinthe

3,4218 gold badges37 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Holtzman Over a year ago

i would also assist user by specifying to tie the objects to their parents (with a With block).

Absinthe Over a year ago

@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.

Collectives™ on Stack Overflow

vba counter loop for columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related