Selecting a range until the last used row

user_4645511 0
Viewed 799

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?

vba excel
2 Answers

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.

user_42791550

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
user_11405790
Related