CODEWITHSUNDEEP
excelvba
rwbarg15
rwbarg15

Reputation: 329

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

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.

Upvotes: 10

Views: 236337

Answers (3)

Aranxo
Aranxo

Reputation: 1183

For a more general approach I suggest following two functions:

Function RangeFromRow2ToEnd(ByRef Sheet As Worksheet, Optional ByVal Cols As String = "") As Range
    Dim Columns As Range

    If Sheet Is Nothing Then Exit Function

    If Cols = "" Then
        Set Columns = Sheet.Range("A1").CurrentRegion
    Else
        Set Columns = Sheet.Columns(Cols)
    End If
    Set RangeFromRow2ToEnd = Application.Intersect(Columns, Sheet.Rows("2:" & LastRow(Sheet)))
End Function

Function LastRow(ByRef Sheet As Worksheet) As Long
    If Sheet Is Nothing Then Exit Function

    LastRow = Sheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Function

Then You can use it like this:

' Select the whole table from row 2:
RangeFromRow2ToEnd(ActiveSheet).Select

' Select only one column from row 2:
RangeFromRow2ToEnd(ActiveSheet, "B").Select

' Select columns A to L from row 2:
RangeFromRow2ToEnd(ActiveSheet, "A:L").Select

Hint: I love the Intersect method, because you can easily define your desired range by defining one range focussing on the columns and another one focussing on the rows and then intersect them both to cut off the non-used cells or the rows/columns you don't want to use.

As example: If you want to get a range for some distinct colums (from row 1), but not to the complete end of possible rows, rather only the used cells, You can intersect it with the UsedRange or the CurrentRegion:

Dim Rng As Range

Set Rng = Application.Intersect(Sheet.Columns("A:L"), Sheet.UsedRange) ' Or:

Set Rng = Application.Intersect(Sheet.Columns("A:L"), Sheet.Range("A1").CurrentRegion)

In this case You even do not need to calc the last row.

Remark: I'm not going into the big discussion which method is the best to find the last row. You can find a lot of variants in the net. For me, the SpecialCells method worked fine in all cases. If you prefer another one, just replace it.

Upvotes: 0

Yehia Amer
Yehia Amer

Reputation: 638

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)

Upvotes: 4

rwisch45
rwisch45

Reputation: 3702

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.

Upvotes: 29

Related Questions