Selecting a range until the last used row

Question

I am trying to select a range until the last used row in the sheet. I currently have the following:

Sub Select_Active_Down()
    Dim lr As Long
    lr = ActiveSheet.UsedRange.Rows.Count
    If Cells(ActiveCell.Row, ActiveCell.Column) = Cells(lr, ActiveCell.Column) Then
        MsgBox "There isn't any data to select."
    Else
        Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(lr, ActiveCell.Column)).Select
        Cells(lr, ActiveCell.Column).Activate
    End If

End Sub

The issue is that I need to select multiple columns, and this will only select the first column of the active range. How can I modify this to select multiple columns rather than just the first?

Range.Resize() could be what you are looking for. https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Resize — DS_London, May 20 '21 at 15:50
Why do you need to `.Select`? It usually only serves to introduce problems unless there is a specific reason for its use. See [How to avoid using select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Ron Rosenfeld, May 20 '21 at 15:51

score 1 · Answer 1 · answered May 20 '21 at 16:04

1

What about selection the entire region? This can be done as follows in VBA:

Selection.CurrentRegion.Select

There also is the possibility to select the entire array. For that, just press Ctrl+G, choose Special and see over there.

answered May 20 '21 at 16:04

Dominique

16,450
15
56
112

score 1 · Answer 2 · answered May 20 '21 at 16:21

I would do this slightly different. I would use .Find to find the last row and the last column (using the same logic shown in the link) to construct my range rather than using Selection | Select | ActiveCell | UsedRange | ActiveSheet.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim rng As Range
    
    '~~> Change it to the relevant sheet
    Set ws = Sheet1
        
    With ws
        '~~> Check if there is data
        If Application.WorksheetFunction.CountA(.Cells) = 0 Then
            MsgBox "No Data Found"
            Exit Sub
        End If
        
        '~~> Find last row
        LastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
        
        '~~> Find last column
        LastColumn = .Cells.Find(What:="*", _
                     After:=.Range("A1"), _
                     Lookat:=xlPart, _
                     LookIn:=xlFormulas, _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious, _
                     MatchCase:=False).Column
                     
        '~~> Construct your range
        Set rng = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        
        '~~> Work with the range
        With rng
            MsgBox .Address
            '
            '~~> Do what you want with the range here
            '
        End With
    End With
End Sub

Selecting a range until the last used row

2 Answers2