Using Command Button to hide & unhide selected Rows

[BobH]

Hi I am trying to use a command button to hide a small range of rows if they are blank and the same button to unhide I am struggling with the code below would appreciate advice on this. The code i have so far is as follows Many Thanks Rob

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide" Then
Dim c As Range
For Each c In Range("B4:b72")
If c.Value = "" Then
   c.EntireRow.Hidden = True
   CommandButton1.Caption = "Show"
   
Else

If CommandButton1.Caption = "Show" Then
 Rows("3:74").Select
    Selection.EntireRow.Hidden = False
   CommandButton1.Caption = "Hide"
Else
End If

End If
Next
End If
End Sub

 

[Fluff]

How about

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide" Then
   Dim c As Range
   For Each c In Range("B4:b72")
      If c.Value = "" Then c.EntireRow.Hidden = True
   Next c
   CommandButton1.Caption = "Show"
   
ElseIf CommandButton1.Caption = "Show" Then
   Rows("3:74").EntireRow.Hidden = False
   CommandButton1.Caption = "Hide"
End If
End Sub

 

[BobH]

Thank you works perfect Cheers Rob

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mark Pearmain]

How about

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide" Then
   Dim c As Range
   For Each c In Range("B4:b72")
      If c.Value = "" Then c.EntireRow.Hidden = True
   Next c
   CommandButton1.Caption = "Show"
  
ElseIf CommandButton1.Caption = "Show" Then
   Rows("3:74").EntireRow.Hidden = False
   CommandButton1.Caption = "Hide"
End If
End Sub

Hi!

I have used your code 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!

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



This is your code I amended to my project.

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

 

[Fluff]

Please start a new thread for this question.
When you do please post your code in tags How to Post Your VBA Code