How to select a range of the second row to the last row

Question

I am currently trying to figure out how to select a range from the second row to the last row, but more specifically between a range of columns. For instance I want to select Range(A2:L2) to the last row of data in the spreadsheet.

I have tried,

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:L2" & Lastrow).Select

But this selects from A2:L2 all the way down to the bottom of the spreadsheet. I have checked to see if Lastrow was incorrect but I printed it to a cell and the correct count of rows displayed.

Answer 1

Try this:

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:L" & Lastrow).Select

Let's pretend that the value of Lastrow is 50. When you use the following:

Range("A2:L2" & Lastrow).Select

Then it is selecting a range from A2 to L250.

Answer 2

Sub SelectAllCellsInSheet(SheetName As String)
    lastCol = Sheets(SheetName).Range("a1").End(xlToRight).Column
    Lastrow = Sheets(SheetName).Cells(1, 1).End(xlDown).Row
    Sheets(SheetName).Range("A2", Sheets(SheetName).Cells(Lastrow, lastCol)).Select
End Sub

To use with ActiveSheet:

Call SelectAllCellsInSheet(ActiveSheet.Name)