Use a variable for multiple ranges in a With/End With statement

[johnnyL]

Trying to save multiple ranges to a variable.

Sub WorkingTest()
'
    With Range("B10:B19", "C10:C19")
        .Value = Evaluate("Row(" & .Address & ") - 9")      ' Prints 1 thru 10 in each range
    End With
End Sub

When I try to use a variable of the saved ranges, I get #VALUE!

Sub NotWorking()
'
    String1 = "B10:B19"
    String2 = "C10:C19"
    String3 = String1 & ", " & String2
'
    With Range(String3)
        .Value = Evaluate("Row(" & .Address & ") - 9")
    End With
End Sub

Any suggestions how to fix what I am doing wrong?

 

[Tetra201]

Try using

With Range(String1, String2)

 

[johnnyL]

Thank you @Tetra201. That does work, however it will not work for the code that I will be using.

The code I will be using searches for columns that qualify. When a column is deemed to qualify, the range of that column is saved. The actual amount of qualifying columns is unknown until the code is ran. That is why I was asking for an approach that would utilize one variable for all of the added column ranges.

 

[Tetra201]

There is nothing wrong with using Range(String3). It's the ROW function that throws the error -- its argument cannot refer to multiple areas.

Was the ROW function used just for testing purposes? -- Or you will need to use it in your actual code?

Update: here is how to modify your NotWorking sub:

Sub NotWorking()
'
    String1 = "B10:B19"
    String2 = "C10:C19"
    String3 = String1 & ", " & String2
'
    For Each c In Range(String3).Areas
        c.Value = Evaluate("Row(" & c.Address & ") - 9")
    Next c
End Sub

 

[johnnyL]

There is nothing wrong with using Range(String3). It's the ROW function that throws the error -- its argument cannot refer to multiple areas.

Was the ROW function used just for testing purposes? -- Or you will need to use it in your actual code?

Spot on @Tetra201!

That was just testing code. When I switched to the actual code, it worked!!!

    With WS.Range(String3))
        .Replace What:="", Replacement:="*", LookAt:=xlPart                                     ' Add '*' to all of the blank cells in the column ranges saved
    End With