Set rng as Multiple Ranges

[Anne Troy]

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]

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]

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]

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]

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]

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")