Simple question on VBA

[dazfoz]

All,

I have a long range in my VBA, and I want to split the line onto multiple rows within VBA but am struggling, as a sample I have;

Range("c4:i16,c10i:17,c21:i28,c31:i56,c59:i59,c62:i62,c65:i65,c68:i68,c71:i71,c74:i75,c78:i79,c82:i83,"c86:i355" etc etc).Select

I need to add quite a few more range references, but am struggling to break the code down, I know that I need to use (Space & Underscore) but cant figure out in what order...

Ive tried the search function to no avail!

Thanks in advance

Darren

 

[Peter_SSs]

The way to break it into separate lines is like this ..

Sub Ver1()
  Range("c4:i16,c17:i17,c21:i28,c31:i56,c59:i59," _
        & "c62:i62,c65:i65,c68:i68,c71:i71,c74:i75," _
        & "c78:i79,c82:i83,c86:i355").Select
End Sub

.. but you may run into errors with the number of separate ranges anyway. I think a better structure is like this.

Sub Ver2()
  Dim rng1 As Range, rng2 As Range, rng3 As Range, rngWhole As Range
  
  Set rng1 = Range("c4:i16,c17:i17,c21:i28,c31:i56,c59:i59")
  Set rng2 = Range("c62:i62,c65:i65,c68:i68,c71:i71,c74:i75")
  Set rng3 = Range("c78:i79,c82:i83,c86:i355")
  Set rngWhole = Union(rng1, rng2, rng3)
  
  rngWhole.Select
End Sub

A further comment is that it is rarely required to actually select a range to work with it and selecting is a relatively slow process.

 

[dazfoz]

Cheers Pete,

I was missing the Ampersand!

Darren

 

[dazfoz]

Pete,

Only just spotted you last comment....do you mean that I can use .clearcontents after defining the range rather than using .select first?

Darren

 

[Peter_SSs]

P..do you mean that I can use .clearcontents after defining the range rather than using .select first?

Exactly!
Much more efficient

Sub Ver2()
  Dim rng1 As Range, rng2 As Range, rng3 As Range, rngWhole As Range
  
  Set rng1 = Range("c4:i16,c17:i17,c21:i28,c31:i56,c59:i59")
  Set rng2 = Range("c62:i62,c65:i65,c68:i68,c71:i71,c74:i75")
  Set rng3 = Range("c78:i79,c82:i83,c86:i355")
  Set rngWhole = Union(rng1, rng2, rng3)
  
  rngWhole.ClearContents
End Sub