With Block Error

[gbjerke]

I have a perplexing With block fail. I am trying to format several non-contiguous ranges.

The Ranges are Declared in a “PublicVariable” module as:

Public WS As Worksheet
Public TotalInactiveDateRowRange As Range
Public TotalActiveDateRowRange As Range

The Ranges are Set in a “SetRanges” module:

Set TotalInactiveDateRowRange = Application.union(WholeMonthInactiveDateRowRange(1), …
Set TotalActiveDateRowRange = Application.union(WholeMonthActiveDateRowRange(1), …

When I check the immediate window with a break after the Set sequence, the ranges are perfect:

?TotalInactiveDateRowRange.Address
$B$3:$H$3,$B$13,$F$53:$H$53,$B$65:$H$65,$B$75:$E$75,$H$115,$B$127:$G$127,$B$137:$G$137,$D$177:$H$177,$B$189:$H$189,$B$199:$C$199,$F$239:$H$239
?TotalActiveDateRowRange.Address
$C$13:$H$13,$B$23:$H$23,$B$33:$H$33,$B$43:$H$43,$B$53:$E$53,$F$75:$H$75,$B$85:$H$85,$B$95:$H$95,$B$105:$H$105,$B$115:$G$115,$H$127,$B$137:$H$137,$B$147:$H$147,$B$157:$H$157,$B$167:$H$167,$B$177:$C$177,$D$199:$H$199,$B$209:$H$209,$B$219:$H$219,$B$229:$H$229

The following statements are not in a loop. The first works perfectly.

With WS.Range(TotalInactiveDateRowRange.Address)
‘ Format stuff here (Nearly Identical in both With blocks. Just RGB color sequence difference)
End With

With WS.Range(TotalActiveDateRowRange.Address)
‘ Format stuff here
End With

The 2nd one (“TotalActiveDateRowRange”) fails with the Error message: Method ‘Range’ of object ‘_Worksheet’ failed. When I place a break on the “With” line and open the Immediate Window the range is there as expected. Intellisense is active, recognizing it as a range and allowing me to select “Address”.

I have searched the project to look for an erroneous or duplicate range declaration, bad spelling, a missing “End” somewhere, etc. I copied and pasted the With block that works and replaced the range name with the one that’s failing to no avail. If I comment out the failing With block the entire code runs perfectly.

Any advice would be much appreciated. I have searched many websites for help and have learned a lot of range setting and methods as well as this error message, but I cannot find out why the first With block works and the second one fails.

 

[dmt32]

glancing at your code I suspect you may have exceeded the limitation of the substring for Range object

If this is the case you can divide the ranges & then combine into one using Union

something like

Set r1 = Range("$C$13:$H$13,$B$23:$H$23,$B$33:$H$33,$B$43:$H$43,$B$53:$E$53," & _
               "$F$75:$H$75,$B$85:$H$85,$B$95:$H$95,$B$105:$H$105,$B$115:$G$115")
       
Set r2 = Range("$H$127,$B$137:$H$137,$B$147:$H$147,$B$157:$H$157,$B$167:$H$167," & _
                "$B$177:$C$177,$D$199:$H$199,$B$209:$H$209,$B$219:$H$219,$B$229:$H$229")

    Union(r1, r2).Select

Dave

 

[gbjerke]

Thank you very much. You are correct. As a workaround, I deconstructed my original union of "WholeMonthDateRowRange" into the 4 separate ranges and when I loop through it work.
Thanks again. I had no idea that there is an upper limit to the substring of a range object.

 

[dmt32]

Most welcome glad solution resolved your issue & appreciate te feedback

Dave