Copying the Last 10 Rows in a Given Column

[keeper10]

Hey all, I need to copy the last 10 rows in column B. Once I do that I am going to paste them into another sheet. I am just new to VBA and don't know how to do this. Thanks!

 

[Gavin T]

Try this:

Sub CopyLines()

    Dim LastRow As Long

    LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("Sheet1").Range("B" & LastRow - 9 & ":B" & LastRow).Copy Sheets("Sheet2").Range("A1")

End Sub

 

[Joe4]

Here is code that will select the last 10 rows in column B (if there are less than 10 rows, it will select all rows).

    'Find last row in column B
    Dim myLastRow As Long
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    'Select last 10 rows in column B
    If myLastRow >= 10 Then
        Range(Cells(myLastRow - 9, "B"), Cells(myLastRow, "B")).Select
    Else
        Range("B1:B" & myLastRow).Select
    End If

It could be written a little shorter, but I broke it out so you can easier understand the logic.

To copy, just change the .Select references with .Copy.