What Variable type to use

[Exceladd1ct]

Hello,

This might be a stupid question but what variable type should be to used for lrow_range variable?

The point is to get the worksheet row number according to the last row of the selected range.

Thank you!

Option Explicit

Sub test_range()
    Dim lrow_range As String 'This works
    'Dim lrow_range As Range 'This gives an error
    
    Dim source As Range
    Dim lrow As Long
    
    Set source = Application.InputBox("Select region", "Get Range", Type:=8)
    
    'Get the last row of the range relative to the worksheet
    lrow_range = source.Cells(source.Rows.Count, source.Columns.Count).Address

    lrow = Range(lrow_range).Row
    
    Debug.Print lrow
End Sub

 

[Fluff]

As long as you are select a single area try

Sub test_range()
    
    Dim source As Range
    Dim lrow As Long
    
    Set source = Application.InputBox("Select region", "Get Range", Type:=8)
    
    'Get the last row of the range relative to the worksheet
    lrow = Split(source.Address, "$")(4)
    
    Debug.Print lrow
End Sub

 

[Exceladd1ct]

As long as you are select a single area try

Sub test_range()
   
    Dim source As Range
    Dim lrow As Long
   
    Set source = Application.InputBox("Select region", "Get Range", Type:=8)
   
    'Get the last row of the range relative to the worksheet
    lrow = Split(source.Address, "$")(4)
   
    Debug.Print lrow
End Sub

I didn't know that is possible to split and get an array element in the same statement in a single line of code.

Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.