CODEWITHSUNDEEP
vba
Charlotte
Charlotte

Reputation: 423

VBA: Select last cell in column range and copy cells in worksheet

I have several columns and I am trying to copy the very last cell of each column into one column (in another worksheet).

This is my code that didn't work (I am looping through rows and columns):

 Sub lastcell()

 Dim lRow As Long
Dim lCol As Long

Dim i As Long


Worksheets("input").Select

 With Worksheets("input")
 Worksheets("output").Cells.ClearContents

 lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

 Set ColRange = .Range(.Cells(1, 1), .Cells(5, lCol))

For Each ccol In ColRange
  lRow = .Cells(.Rows.Count, ccol).End(xlUp).Rows.Count

   For i = 2 To 6
    Worksheets("output").Cells(i, 1) = .Cells(lRow, ccol)

 Next i
Next ccol


End With

End Sub

Upvotes: 0

Views: 2713

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

You have one too many loops.

The lRow = .Cells(.Rows.Count, ccol).End(xlUp).Rows.Count should end with Row not .Rows.Count

Also with Set ColRange = .Range(.Cells(1, 1), .Cells(5, lCol)) you are going to loop through each column 5 times. The 5 should be a 1

There is no need to acivate or select the input sheet at the beginning of the code.

ccol should be declared

Sub lastcell()

Dim lRow As Long
Dim lCol As Long
Dim ccol as Range
Dim i As Long   

With Worksheets("input")
    Worksheets("output").Cells.ClearContents
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    Set colrange = .Range(.Cells(1, 1), .Cells(1, lCol))
    i = 1
    For Each ccol In colrange
        lRow = .Cells(.Rows.Count, ccol.Column).End(xlUp).Row
        Worksheets("output").Cells(i, 1).Value = .Cells(lRow, ccol.Column).Value
        i = i + 1
    Next ccol


End With

End Sub

We can simplify it even further with a simple for loop:

Sub lastcell()

Dim lRow As Long
Dim lCol As Long
Dim i As Long

With Worksheets("input")
    Worksheets("output").Cells.ClearContents
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    For i = 1 To lCol
        lRow = .Cells(.Rows.Count, i).End(xlUp).Row
        Worksheets("output").Cells(i, 1).Value = .Cells(lRow, i).Value
    Next i

End With

Just for an FYI, this can also be done with a formula.

In your first cell on the output sheet put this formula:

=INDEX(input!A:Z,MAX(IFERROR(MATCH("ZZZ",INDEX(input!A:Z,0,ROW(1:1))),0),IFERROR(MATCH(1E+99,INDEX(input!A:Z,0,ROW(1:1))),0)),ROW(1:1))

And then copy/drag the formula down till you get 0s

Upvotes: 1

Related Questions