CODEWITHSUNDEEP
excelvba
Nathan
Nathan

Reputation: 13

vba counter loop for columns

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

Upvotes: 1

Views: 595

Answers (1)

Absinthe
Absinthe

Reputation: 3391

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

Upvotes: 1

Related Questions