Search code examples
excelvbaline-breaks

Line break from a list and clean this code for conciseness

I have the following code which is pretty long and wish to break it in three lines but the space and "-" doesn't work:

Range("C8:D38,H8:I38,M8:N38,R8:S38,W8:X38,AB8:AC38,AG8:AH38,AL8:AM38, _
    AQ8:AR38 , AV8:AW38 , BA8:BB38 , BF8:BG38 , BK8:BL38 , BP8:BQ38 , BU8:BV38 , BZ8:CA38 , _
        CE8: CF38 , CJ8: CK38 , CO8: CP38 , CT8: CU38 , CY8: CZ38 , DD8: DE38 ").ClearContents ""
Solution

  • Close the section with a quote and concatenate subsequent quoted sections on to the end.

    Range("C8:D38,H8:I38,M8:N38,R8:S38,W8:X38,AB8:AC38,AG8:AH38,AL8:AM38," & _
      "AQ8:AR38,AV8:AW38,BA8:BB38,BF8:BG38,BK8:BL38,BP8:BQ38,BU8:BV38," & _
      "BZ8:CA38,CE8:CF38,CJ8:CK38,CO8:CP38,CT8:CU38,CY8:CZ38,DD8:DE38").ClearContents

    Alternate:

    dim rng as range, i as long

set rng = Range("C8:D38")
for i = 8 to 108 step 5
    set rng = union(rng, cells(8, i).resize(31, 2))
next i
debug.print rng.address(0, 0)