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.

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




--
Gary's Student


"Hector Fernandez" wrote:

> 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.
>
>

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")
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Hector Fernandez" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

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.

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

Mike F
"Hector Fernandez" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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"
<(E-Mail Removed)>
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.

Did you try that for this case?

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Jim Cone" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
> <(E-Mail Removed)>
> 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,
'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" <(E-Mail Removed)>
wrote in message
Did you try that for this case?
--
Don Guillett
SalesAid Software
(E-Mail Removed)


"Jim Cone"
<(E-Mail Removed)>
wrote in message
> 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"
> <(E-Mail Removed)>
> 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.
>

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"

--


Gary


"Jim Cone" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> 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" <(E-Mail Removed)>
> wrote in message
> Did you try that for this case?
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
>
>
> "Jim Cone"
> <(E-Mail Removed)>
> wrote in message
>> 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"
>> <(E-Mail Removed)>
>> 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, 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