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.

score 29 · Accepted Answer · answered Jun 26 '13 at 17:59

29

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.

answered Jun 26 '13 at 17:59

rwisch45

3,692
2
25
36

Why is `Range("A2:L2" & Lastrow).Select` selecting from A2 to L250 if Lastrow is 50? I would appreciate an explanation on what math Excel is doing here. – TylerH Feb 10 '17 at 18:46
2

@TylerH Because we start with "L2" and when we & 50 to it, it's like saying "L2" & "50" which equals "L250" – RobertSF Oct 12 '17 at 17:16
1

@RobertSF Ah, it's concatenating the values, thanks. – TylerH Oct 12 '17 at 18:01
This worked for me except I had to use xlDown instead of xlUp – SendETHToThisAddress May 23 '19 at 19:39

Yehia Amer · Answer 2 · 2017-03-14T21:01:45.893

4

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)

edited Mar 14 '17 at 21:01

answered Mar 14 '17 at 20:36

Yehia Amer

608
6
11

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

2 Answers2

Linked