View Full Version : Set Range Using Variables
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
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
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.