Proper /Shorter code to select a Range from after Last Row till end of usedRange, VBA?-CodePudding

I need to select a Range from after Last Row till end of usedRange. The below code works, But is there Proper /Shorter code.

Option Explicit

Sub Select_Blank_in_Usedrange()

 Dim ws As Worksheet: Set ws = ActiveSheet
 Dim LastR As Long, LastR_UsedRange As Long

 LastR = ws.Cells(Rows.Count, "A").End(xlUp).Row
 LastR_UsedRange = ws.UsedRange.Rows.Count
 
 ws.Range("A" & LastR   1, "AE" & LastR_UsedRange).Select

End Sub

CodePudding user response：

If the code works and has no redundant parts, I would say it's good. If I were to suggest an improvement, it would be to save the relevant addresses as Range Objects instead of Row numbers. This way you can assemble the larger range directly from the two corners, instead of having to concatenate the address in the final step.

Sub Select_Blank_in_Usedrange()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim TopCorner As Range
    Set TopCorner = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
    
    Dim BottomCorner As Range
    With ws.UsedRange.Columns(31)
       Set BottomCorner = .Cells(.Cells.Count)
    End With
    
    ws.Range(TopCorner, BottomCorner).Select

End Sub

To me, this is much clearer, and the constants are clearly displayed. This will make it easier to edit later, if the number of columns changes, or if the starting column moves from "A".

CodePudding user response：

The shortest code will be the next:

 Dim ws As Worksheet: Set ws = ActiveSheet
 ws.Range("A" & ws.Range("A" & ws.rows.count).End(xlUp).row   1, _
        "AE" & ws.UsedRange.rows.count   ws.UsedRange.cells(1).row - 1).Select

It will also deal with the case when UsedRange does not start from the first row...