Multiple Ranges In Code

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,450
Office Version
  1. 2007
Platform
  1. Windows
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

Code:
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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).
Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Code:
[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]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.

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

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,450
Office Version
  1. 2007
Platform
  1. Windows
Many thanks both,

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

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
[B][COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] 23[/B]
to this:
Code:
[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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,313
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top