Hide/unhide rows

[karolina1406]

hi All,
I have a problem with the workbook I work on. In one Tab (Tab1) with command buttons (Names A,B,C and D). The second tab is a table with a data for A, B, C and D. I want to hide all rows with 0 in column C in Tab2 if I press command button 1

I have a code which works in the development mode but doesn't work whe nI actually use Command buttons:

Dim r As Long
For r = 3 To 35
If Cells(r, 3) = 0 Then
Rows(r).EntireRow.Hidden = True
Else
Rows(r).EntireRow.Hidden = False
End If
Next r

Any help will be much appreciated.

 

[karolina1406]

that's great! It now works but I had to switch off this automatic calc.... Thank you very much for a help. Really useful learning

 

[Fluff]

You're welcome & thanks for the feedback

 

[karolina1406]

hi again
I was wondering if you know any tricks/hints/codes which would speed up my macro. I have applied above to a different model and it works perfectly fine other than it's very slow

 

[Michael M]

Try using

Sub karolina1406()
Dim Rw As Long
Application.ScreenUpdating = False
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
Application.ScreenUpdating = True
End Sub

 

[karolina1406]

Try using

Sub karolina1406()
Dim Rw As Long
Application.ScreenUpdating = False
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
Application.ScreenUpdating = True
End Sub

Already got this and its still slow The only thing what speeds it up at the moment is if I switch off automatic calc however that then means that the command button needs to be pressed twice to work

 

[Michael M]

You don't have any worksheet_events running as well, do you ??
If you turn autocalc off and back on at the end of the code shouldn't that then run correctly ??

Sub karolina1406()
Dim Rw As Long
  with Application
   .ScreenUpdating = False
   .calculation=xlmanual
  end with
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
  with Application
    .ScreenUpdating = true
    .calculation=xlautomatic
  end with
End Sub

 

[steve the fish]

You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

Dim i As Long, arr, rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet2")
    .Rows("3:35").Hidden = False
    arr = .Range(.Cells(3, 3), .Cells(35, 3))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + 2, 1))
            Else
                Set rng = .Cells(i + 2, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

 

[karolina1406]

You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

Dim i As Long, arr, rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet2")
    .Rows("3:35").Hidden = False
    arr = .Range(.Cells(3, 3), .Cells(35, 3))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + 2, 1))
            Else
                Set rng = .Cells(i + 2, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

sorry for being a Blonde but which part of this code does hide all the rows if in column C I have 0 "zero"?

 

[steve the fish]

This bit:

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

 

[karolina1406]

great, that's fine... I tried to apply the same code for another tab (within this same command button) which is called "Pogramme_Plan" and the filtering (0,1) is in column A from row 9. I applied below:

With Sheets("Programme_Plan")
.Rows("9:500").Hidden = False
.Range("a7").Value = Sheets("Summary").Range("d7")
arr = .Range(.Cells(3, 3), .Cells(500, 1))
For i = LBound(arr) To UBound(arr)
If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i + 2, 1))
Else
Set rng = .Cells(i + 2, 1)
End If
End If
Next
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True


but I am getting an error at the

Set rng = Union(rng, .Cells(i + 2, 1)) line... any idea why?