Using Command Button to hide & unhide selected Rows in a table

[Mark Pearmain]

Hi!

I have used your code from another thread which works really well, however, I am running this on 250 rows and this takes quite some time to execute when hiding the rows, whereas the show part is instant. I tried running a macro to "TurnOffStuff" which turns off screen updating and calculations but this did not help. Please can you suggest any improvements to make this quicker?

many thanks for your help!

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide" Then
Sub TurnOffStuff()
Dim c As Range
For Each c In Range("D8:D267")
If c.Value = "" Then c.EntireRow.Hidden = True
Sub TurnOnStuff()

Next c
CommandButton1.Caption = "Show"
Sub TurnOffStuff()

ElseIf CommandButton1.Caption = "Show" Then
Rows("8:267").EntireRow.Hidden = False
CommandButton1.Caption = "Hide"
Sub TurnOnStuff()

End If
End Sub

These are my helper macros:

Sub TurnOffStuff()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
End Sub

Sub TurnOnStuff()
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Does column D contain formulae?

 

[Mark Pearmain]

Hi & welcome to MrExcel.
Does column D contain formulae?

Hi Fluff, Yes it contains a concatenated text string which is pulled from four other worksheets within the workbook.

 

[Fluff]

Ok, how about

Private Sub CommandButton1_Click()
   Dim c As Range, Rng As Range
   
   Sub TurnOffStuff()
   If CommandButton1.Caption = "Hide" Then
      For Each c In Range("D8:D267")
         If c.Value = "" Then
            If Rng Is Nothing Then Set Rng = c Else Set Rng = Union(Rng, c)
         End If
      Next c
      If Not Rng Is Nothing Then Rng.EntireRow.Hidden = True
   ElseIf CommandButton1.Caption = "Show" Then
      Rows("8:267").EntireRow.Hidden = False
      CommandButton1.Caption = "Hide"
   End If
   Sub TurnOnStuff()
End Sub

 

[Mark Pearmain]

Hi Fluff,
Thanks for the quick reply!

The code works instantly to hide the blank rows, however the button does not revert to "Show" once the rows are hidden so I cannot unhide them.

Private Sub CommandButton1_Click()
   Dim c As Range, Rng As Range
   
   Call TurnOffStuff
   If CommandButton1.Caption = "Hide" Then
      For Each c In Range("D8:D267")
         If c.Value = "" Then
            If Rng Is Nothing Then Set Rng = c Else Set Rng = Union(Rng, c)
         End If
      Next c
      If Not Rng Is Nothing Then Rng.EntireRow.Hidden = True
   ElseIf CommandButton1.Caption = "Show" Then
      Rows("8:267").EntireRow.Hidden = False
      CommandButton1.Caption = "Hide"
   End If
   Call TurnOnStuff
End Sub

 

[Fluff]

You need to add

CommandButton1.Caption = "Show"

after the Next c line

 

[Mark Pearmain]

You need to add

CommandButton1.Caption = "Show"

after the Next c line

you are a legend, thankyou!

 

[Fluff]

Glad to help & thanks for the feedback.