Multiple Ranges In Code

[ExcelRoy]

Hi All,

I have the following Ranges within the code below but it seems to be too much as I get an error "Run Time Error 1004"

I think it may be because there is 24 sets of ranges

Is there a better way to have this work with the ranges but make the row shorter and avoid the error

Private Sub Worksheet_Calculate()
Dim c As Range
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With
With Range("W8:W115, W118:W225, W228:W335, W338:W445, W448:W555, W558:W665, W668:W775, W778:W885, W888:W995 , W998:W1105 , W1108:1215 , W1218:W1325 , W1328:W1435 , W1438:W1545 , W1548:W1655 ,W1658:W1765 , W1768:W1875 , W1878:W1985 , W1988:W2095 , W2098:W2205, W2208:W2315, W2318:W2425, W2428:W2535, W2538:W2645 ")
    .Cells.EntireRow.Hidden = False
    For Each c In .Cells
        If c.Value = "" Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With
End Sub

 

[Joe4]

Because the jump in the rows with each range is the same each time (110), we can use a loop to do this.
There are 24 different ranges, so we loop through 24 times (0 to 23).

Private Sub Worksheet_Calculate()

    Dim c As Range
    Dim fRow As Long
    Dim lRow As Long
    Dim i As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    For i = 0 To 23
        fRow = (i * 110) + 8
        lRow = (i * 110) + 115
        With Range(Cells(fRow, "W"), Cells(lRow, "W"))
            .Cells.EntireRow.Hidden = False
            For Each c In .Cells
                If c.Value = "" Then
                    c.EntireRow.Hidden = True
                Else
                    c.EntireRow.Hidden = False
                End If
            Next c
        End With
    Next i
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    
End Sub

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Calculate()
[color=darkblue]Dim[/color] c [color=darkblue]As[/color] Range, i [color=darkblue]As[/color] [color=darkblue]Long[/color]
[color=darkblue]With[/color] Application
    .ScreenUpdating = [color=darkblue]False[/color]
    .Calculation = xlCalculationManual
    .EnableEvents = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]
[B][color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] 23[/B]
[B]    [color=darkblue]With[/color] Range("W8:W115").Offset(i * 110)[/B]
        .Cells.EntireRow.Hidden = [color=darkblue]False[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] c [color=darkblue]In[/color] .Cells
            [color=darkblue]If[/color] c.Value = "" [color=darkblue]Then[/color]
                c.EntireRow.Hidden = [color=darkblue]True[/color]
            [color=darkblue]Else[/color]
                c.EntireRow.Hidden = [color=darkblue]False[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] c
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[B][color=darkblue]Next[/color] i[/B]
[color=darkblue]With[/color] Application
    .ScreenUpdating = [color=darkblue]True[/color]
    .Calculation = xlCalculationAutomatic
    .EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[ExcelRoy]

Perfect many thanks Joe4

 

[Joe4]

You are welcome.

I really like AlphaFrog's version. Same concept, but a little cleaner (no need for fRow and lRow calculations).

 

[ExcelRoy]

Many thanks both,

Would I be able to increase to 50 ranges, is this possible?

Thanks

 

[Joe4]

Would I be able to increase to 50 ranges, is this possible?

If they keep following the same pattern, this is pretty easy.

Regardless of which code you use (mine or Alphafrog's), simply increase the loop, i.e.
Change this:

[B][COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] 23[/B]

to this:

[B][COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] 49[/B]

(the last number will always be one less than the number of loops you have).

 

[ExcelRoy]

Brilliant many thanks