PDA

View Full Version : [SOLVED] Set rng as Multiple Ranges



Anne Troy
03-29-2005, 09:58 AM
I want to use the following code, but I cannot find an example of setting the range to multiple ranges, while breaking up the line of code.



Set rng = Range("F19:F37,P19:P37,F46:F64,P46:P65,F73:F91,F136:F154,


Yes, it continues for about a mile. How do I break up that line in my VBE window?

I tried space underscore, but it doesn't seem to like that.

mvidas
03-29-2005, 10:19 AM
Hi Anne,
I know you have many more ranges to add, but I'll use the range you provided as an example. Since "F19:F37,P19:P37,F46:F64,P46:P65,F73:F91,F136:F154" is really just a string, you would use normal concatenation for it, like:


Set Rng = Range("F19:F37," & _
"P19:P37," & _
"F46:F64," & _
"P46:P65," & _
"F73:F91," & _
"F136:F154")

Or you could use the union statement, like:


Set Rng = Union([f19:f37], _
[p19:p37], [f46:f64], _
[p46:p65], [f73:f91], _
[f136:f154])

Or even a combination of union and intersect (union for the 2 columns, intersect to get the intersection of those columns and the specific rows):


Set Rng = Intersect(Range("19:37,46:64,73:91,136:154"), _
Union(Columns("F"), Columns("P")))
Matt

Anne Troy
03-29-2005, 10:24 AM
Hi Anne,
I know you have many more ranges to add, but I'll use the range you provided as an example. Since "F19:F37,P19:P37,F46:F64,P46:P65,F73:F91,F136:F154" is really just a string,

That's the ticket. THANKS!!!!

mvidas
03-29-2005, 10:46 AM
If it is always F and P for the same rows, you'll probably find it easier to use the Intersect and Union method, as it would be less things to change later on if you need to and easier (to my eyes, at least) to read. :)
Glad to help!

Anne Troy
03-29-2005, 01:02 PM
It's not. :(

I get an error when I run this:


Dim rng As Range
Dim cll As Range
Dim howmuch
howmuch = InputBox("By how much shall we multiply the XXX range?", "Enter Value", 0)
If Not IsNumeric(howmuch) Then
MsgBox "Invalid entry.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Set rng = Range("F19:F37" & _
"P19:P37" & _
"F46:F64" & _
"P46:P65" & _
"F73:F91" & _
"F136:F154" & _
"P136:P154" & _
"F163:F181" & _
"P163:P181" & _
"F190:F208" & _
"F252:F271" & _
"P252:P271" & _
"F280:F298" & _
"P280:P298" & _
"F487:F516" & _
"P487:P516" & _
"F525:F554" & _
"F604:F629" & _
"P605:P629" & _
"F638:F663" & _
"P638:P663" & _
"F721:F763" & _
"P721:P763")
Application.ScreenUpdating = False
For Each cll In rng.Cells
cll.Value = cll.Value + howmuch
Next cll
Application.ScreenUpdating = False
End Sub

Error: method range of object global failed

:(

mvidas
03-29-2005, 01:07 PM
Looks like you forgot some commas in there to separate the individual ranges :)

Also, as I personally don't like a lot of lines of code (even though my example to you showed those), you could condense yours down to

Set rng = Range("F19:F37,P19:P37,F46:F64,P46:P65,F73:F91,F136:F154,P136:P154," & _
"F163:F181,P163:P181,F190:F208,F252:F271,P252:P271,F280:F298,P280:P298,F487: F516," & _
"P487:P516,F525:F554,F604:F629,P605:P629,F638:F663,P638:P663,F721:F763,P721: P763")