VBA code runs slow with check box

[Lisa116]

Hi all,

Is there a better code for hiding rows based on 1 criteria when the check box is marked? Below is what I have and it takes close to a minute to cycle through:
Sub CheckBox1_Click()
'"Hide unused plans" on Medical FI sheet
If Range("S1").Value = "True" Then
Call Hide
ElseIf Range("S1").Value = "False" Then
Call Unhide
Else
Return
End If
End Sub

Sub Hide()
'Hides unused plans on Medical FI sheet
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim c As Range
For Each c In Range("N29:N210,N220:N375,N539:N720,N923:N1104,N1114:N1295")
If c.Value = "" Then Rows(c.Row).Hidden = True
Next
Range("A25").Select
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Thanks, Lisa116

 

[Anthony47]

Try changing the following lines

Dim c As Range
For Each c In Range("N29:N210,N220:N375,N539:N720,N923:N1104,N1114:N1295")
If c.Value = "" Then Rows(c.Row).Hidden = True
Next

to

Dim c As Range, rToHide As Range
For Each c In Range("N29:N210,N220:N375,N539:N720,N923:N1104,N1114:N1295")
    Debug.Print c.Address, c.Value
    If c.Value = "" Then
        If rToHide Is Nothing Then
            Set rToHide = c
        Else
            Set rToHide = Application.Union(rToHide, c)
        End If
    End If
Next
rToHide.EntireRow.Hidden = True

Bye

 

[Lisa116]

Anthony,

Thank you! Works great.

Lisa116

 

[DanteAmor]

If you always have a value in cell N28, you could use the following:

Sub Hide()
  On Error Resume Next
  Range("N28:N210,N220:N375,N539:N720,N923:N1104,N1114:N1295").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

 

[DanteAmor]

You can actually start at N29, try this:

Sub Hide()
  On Error Resume Next
  Range("N29:N210,N220:N375,N539:N720,N923:N1104,N1114:N1295").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub