PDA

View Full Version : Set Range Using Variables



BrI
06-12-2017, 09:06 AM
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
06-12-2017, 09:12 AM
ws.Range(r1 & ":" & r2).ClearContents

Bob Phillips
06-12-2017, 09:14 AM
You could also use


ws.Range(r1, r2).ClearContents

BrI
06-12-2017, 09:28 AM
Excellent! works, thanks very much

Paul_Hossler
06-12-2017, 11:00 AM
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
06-12-2017, 11:13 AM
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
06-12-2017, 12:45 PM
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
06-12-2017, 02:50 PM
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
06-12-2017, 11:53 PM
Although I quoted you Paul, it was intended more as a corollary to your statements, for the OP's edification :)