Set Range Using Variables

[BrI]

I need to use variables to define a range. Sorry as this is very basic, but can't get to work.

Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")

'This works:
   ws.Range("A1:C10").ClearContents


'But tried variations similar to below using variables and not working:
    r1 = "A" & RowVariable1
    r2 = "C" & RowVariable2

ws.Range("r1:r2").ClearContents

How can I set a range using variables r1 & r2 ?

[Bob Phillips]

ws.Range(r1 & ":" & r2).ClearContents

[Bob Phillips]

You could also use

ws.Range(r1, r2).ClearContents

[BrI]

Excellent! works, thanks very much

[Paul_Hossler]

I need to use variables to define a range. Sorry as this is very basic, but can't get to work.

How can I set a range using variables r1 & r2 ?

I think it'll depend on how r1 and R2 are Dim-ed, that is either as Range or as String (or maybe Variant since your macro doesn't Dim them)

Option Explicit
Sub AsString()
    Dim ws As Worksheet
    Dim r1 As String, r2 As String
    Dim RowVariable1 As Long, RowVariable2 As Long
    Set ws = Worksheets("Sheet1")
 
    'This works:
    'ws.Range("A1:C10").ClearContents
 
    RowVariable1 = 1
    RowVariable2 = 15
 
 
    'But tried variations similar to below using variables and not working:
    r1 = "A" & RowVariable1
    r2 = "C" & RowVariable2
 
    ws.Range(r1 & " :" & r2).ClearContents
End Sub


Sub AsRange()
    Dim ws As Worksheet
    Dim r1 As Range, r2 As Range
    Dim RowVariable1 As Long, RowVariable2 As Long
    Set ws = Worksheets("Sheet1")
 
    'This works:
    'ws.Range("A1:C10").ClearContents
 
    RowVariable1 = 1
    RowVariable2 = 15
 
 
    'But tried variations similar to below using variables and not working:
    Set r1 = ws.Range("A" & RowVariable1)
    Set r2 = ws.Range("C" & RowVariable2)
    
    Range(r1, r2).ClearContents
End Sub

[BrI]

Thanks, I had been wondering how to Dim the variables. I went with a string and (r1, r2) worked with the string Dim.

[Bob Phillips]

I think it'll depend on how r1 and R2 are Dim-ed, that is either as Range or as String (or maybe Variant since your macro doesn't Dim them)

If they had been range objects, he would have gotten a compile error as he isn't Set'ting them.

[Paul_Hossler]

If they had been range objects, he would have gotten a compile error as he isn't Setting them.

True, but all I saw was "Can't get it to work" and "How can I set a range using ..." so I went with a "wordy, try to cover all bases" suggestion

[Bob Phillips]

Although I quoted you Paul, it was intended more as a corollary to your statements, for the OP's edification