Select a range of cells by Rows or Columns from a given variable-CodePudding

A1:A10 is general way for range in excel

can we store A1 and A10 in variable and access it like

num1 = A1
num2 = A10
num1:num2?

Is this possible in excel vba?

I have tried this but getting error

Sub check()

Dim num1, num2 As Long
Dim rng As Range

num1 = Range("A1").Value
num2 = Range("A10").Value

Set rng = Range(num1 & ":" & num2)
Debug.Print (rng)
    
End Sub

CodePudding user response：

It looks like this is what you are after?

Sub checkAsString()

    Dim rng As Range
    Dim num1 As String, num2 As String

    num1 = "A1"
    num2 = "A2"
    strAddress = Trim(num1 & ":" & num2)

    Set rng = Range(strAddress)
    
End Sub

Or, if you really want to work with numbered ranges it is likely because you want to work with entire rows or columns. In that case see below:

Sub checkAsLong()

    Dim rng As Range, address As Range
    Dim str1 As String, str2 As String
    Dim num1 As Long, num2 As Long

    str1 = "A1"
    str2 = "A2"

    num1 = CLng(Range(str1).Value)
    num2 = CLng(Range(str2).Value)

    'assuming you want to select whole Rows...
    Set rng = Range(Rows(num1), Rows(num2))

    'assuming you want to select whole Columns...
    Set rng = Range(Columns(num1), Columns(num2))

End Sub