clear an array of ranges using a single line of code

Question

i have made global string constants for ranges.

Global Const Graph_Cl As String = "G2:G13"
Global Const Graph_Vl As String = "I2:I13"
Global Const Graph_VlS As String = "L2:L13"

i want to clear these ranges using a single line of code.

With wkDataToUse
    .Range(Graph_Cl, Graph_Vl, Graph_VlS).ClearContents
End With

but i get error:

"Wrong no of arguments or invalid property assignment!"

i even tried this:

With wkDataToUse
    .Range(Array(Graph_Cl, Graph_Vl, Graph_VlS)).ClearContents
End With

OR

With wkDataToUse
    .Range([{Graph_Cl, Graph_Vl, Graph_VlS}]).ClearContents
End With

how can i clear all the ranges in one line of code?

Alex K. · Accepted Answer · 2012-05-12 12:00:08Z

4

To pass multiple range addresses you need the comma delimited string format: "a:b,c:d,e:f" so you can;

.Range(Graph_Cl & "," & Graph_Vl & "," & Graph_VlS).ClearContents

You could also;

Union(.Range(Graph_Cl), .Range(Graph_Vl), .Range(Graph_VlS)).ClearContents

answered May 12, 2012 at 12:00

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1