Using variables in the Range command

  • Thread starter Thread starter Chi Man Wong
  • Start date Start date
C

Chi Man Wong

Usually the Range method looks like:
Range("A1:C50")

In my case, the first and last column are variables. How can I still
use the Range method? I need to find a command to define my range
like:

dim rgRange as range

rgRange = sheetname.range( (row i, column j): (row m, column n) )

any help?
 
Sub test()

Dim Row1 As Long
Dim Col1 As Long
Dim Row2 As Long
Dim Col2 As Long
Dim rngRange As Range

Set rngRange = _
Sheets(1).Range(Cells(Row1, Col1), _
Cells(Row2, Col2))

End Sub

RBS
 
A minor modification of RBS's solution--just in case the activesheet wasn't
sheets(1):

Sub test()

Dim Row1 As Long
Dim Col1 As Long
Dim Row2 As Long
Dim Col2 As Long
Dim rngRange As Range

col1 = 3
row1 = 1
col2 = 5
row2 = 7

with sheets(1)
Set rngRange = .Range(.Cells(Row1, Col1), .Cells(Row2, Col2))
end with

End Sub

Note the extra dots with the .cells().
 
Back
Top