Breaking up long line of code doesn't work

[Hector Fernandez]

I am trying to perform a single event to multiple ranges but for some
reason I can't get the macro to work if I write it on multiple lines.

If, for example, I write:

Sub Clear_Ranges()

Range("range1,range2,range3") = clear

End Sub

The macro works just fine and clears the ranges. However, if I do the
following:

Sub Clear_Ranges()

Range("range1, _
range2, _
range3") = clear

End Sub

I get an error message "Compile Error: Expected: List separator or )"

I thought that using a space and underscore was suppose to allow you to
write a single line of code across several lines in order to make it
easier to read or fit in a page.

I intend to have a lot more than three ranges in the macro and it will
be very messy and long if I try to put it on a single line.

What am I doing wrong?
Is there an easier way to perform the same thing on several different
ranges?

Thanks.

 

[Guest]

Try UNION()


Sub lots_of_ranges()
Dim range1 As Range, range2 As Range, range3 As Range, r As Range
Set range1 = Range("A1:A10")
Set range2 = Range("B4:b56")
Set range3 = Range("Z100:Z103")

Set r = Union(range1, range2, range3)
r.Clear
End Sub

 

[Don Guillett]

Usually but it appears, in this case, no. So, just make several lines..
Range("range1,range2,range3") = clear
Range("range4,range5,range6") = clear
Of course, if contiguos, not necessary.

range("a2, c2:f2 ,l2")

 

[Hector Fernandez]

Gary, thanks for the response - unfortunately your method is more work
than simply writing

Range("range1") = clear
Range("range2") = clear
....and so forth

Is there some reason why the approach you suggest would be better?

Don, unless someone comes up with an alternative, I may have to resort
to your suggestion.

Thanks.

 

[Mike Fogleman]

You should not use line continuation within a parentheses. You should use
Don's suggestion.

Mike F

 

[Jim Cone]

Hector,
That is a text string you are breaking up.
You need quote marks around each segment.
Using just line breaks won't work.

"This is a very long text string" can be split into three lines this way...
"This is a very " & _
"long " & _
"text string"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Hector Fernandez"
<[email protected]>
wrote in message
I am trying to perform a single event to multiple ranges but for some
reason I can't get the macro to work if I write it on multiple lines.

If, for example, I write:
Sub Clear_Ranges()
Range("range1,range2,range3") = clear
End Sub

The macro works just fine and clears the ranges. However, if I do the
following:
Sub Clear_Ranges()
Range("range1, _
range2, _
range3") = clear
End Sub

I get an error message "Compile Error: Expected: List separator or )"
I thought that using a space and underscore was suppose to allow you to
write a single line of code across several lines in order to make it
easier to read or fit in a page.
I intend to have a lot more than three ranges in the macro and it will
be very messy and long if I try to put it on a single line.
What am I doing wrong?
Is there an easier way to perform the same thing on several different
ranges?
Thanks.

 

[Don Guillett]

Did you try that for this case?

 

[Jim Cone]

Don,
'rng1, rng2, rng3 are named ranges... A1, B2, C3

Sub AAA()
Range("rng1," & _
"rng2," & _
"rng3").Value = "Hector"
End Sub

Sub BBB()
Range("rng1," & _
"rng2," & _
"rng3").Clear
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Don Guillett" <[email protected]>
wrote in message
Did you try that for this case?
--
Don Guillett
SalesAid Software
(e-mail address removed)


"Jim Cone"

 

[Gary Keramidas]

i've use something like this before, seemed to work fine. using the same method
you show here

Range("this_is_range1,this_is_range2," & _
"this_is_range3,this_is_range4,this_is_range5, " & _
"this_is_range6,this_is_range7").Value = "hector"

 

[Hector Fernandez]

Don, I live in Spain so it's late now, but will try Jim and Gary's
recommendations tomorrow morning (my time) and let you guys know how it
went.

Thanks to all of you.

Héctor

 

[Hector Fernandez]

Don, yes, it works great.

Thanks to all.

Héctor

 

[Don Guillett]

Please post your final solution for all to see

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don, yes, it works great.

Thanks to all.

Héctor

 

[ilia]

Sub Clear_Ranges()
Range("range1, " & _
"range2, " & _
"range3") = clear
End Sub

 

[Hector Fernandez]

As Don requests, here is the solution I used:

Sub Clear_Range_Revenues()
Range("TypeA_Rev1," & _
"TypeA_Rev2," & _
"TypeA_Rev3," & _
"TypeA_Rev4," & _
"TypeA_Rev5," & _
"TypeA_Rev6," & _
"TypeA_Rev7," & _
"TypeA_Rev8," & _
"TypeA_Rev9," & _
"TypeA_Rev10," & _
"TypeA_Rev11," & _
"TypeA_Rev12," & _
"TypeA_Rev13," & _
"TypeA_Rev14," & _
"TypeA_Rev15," & _
"TypeA_Rev16," & _
"TypeA_Rev17," & _
"TypeA_Rev18," & _
"TypeA_Rev19," & _
"TypeA_Rev20") = Clear

Again, thanks to all who pitched in.