clearing contents of array of ranges using a single line of code

[Junoon]

Hi,

i have made global string constants for ranges.



Global Const G_Cl As String = "G2:G13"
Global Const G_Vl As String = "I2:I13"
Global Const G_VlS As String = "L2:L13"


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



With wkData
.Range(G_Cl, G_Vl, G_VlS).ClearContents
End With


but i get error:


"Wrong no of arguments or invalid property assignment!"


i even tried this:



With wkData
.Range(Array(G_Cl, G_Vl, G_VlS)).ClearContents
End With


OR



With wkData
.Range([{G_Cl, G_Vl, G_VlS}]).ClearContents
End With


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

[Admin]

Hi,

Try with UNION


Union(.Range(G_Cl), .Range(G_Vl), .Range(G_VlS)).ClearContents

[Rick Rothstein]

i have made global string constants for ranges.



Global Const G_Cl As String = "G2:G13"
Global Const G_Vl As String = "I2:I13"
Global Const G_VlS As String = "L2:L13"


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



With wkData
.Range(G_Cl, G_Vl, G_VlS).ClearContents
End With


but i get error:

If you did it without using the constants, this is how the code line would look...


.Range("G2:G13,I2:I13,L2:L13").ClearContents

To duplicate this structure, you have to concatenate the constants together using commas between them in order to get the right syntax for the multiple ranges within the Range call. Try it like this...


With wkData
.Range(G_Cl & "," & G_Vl & "," & G_VlS).ClearContents
End With

[Junoon]

Hi,

Thank you for your solutions. this has definitely resolved my issue. please consider this thread as resolved. :)

[Admin]

Hi Junoon,

Thanks for the feedback. :cheers: