what code to use to hide to hide rows showing 0

[nadirv8]

Hi All. Just a quick one... Basically I just want the button to look through my range (A16:A416) and hide the EntireRow that is showing 0 in the column A range. Any idea the VBA code for it?

like - search, select, hide.


Thankyou very much

 

[macajm]

try this code snippet
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd

 

[nadirv8]

Thankyou. Thing is, I do know about the Autofilter usage but really wanted to avoid using it. Also wanted to avoid using 2 buttons - turning it off and on.

Does anyone know the code? pretty please

 

[riaz]

The following will look at a16 to a416 and hide rows where cell in col A is 0. Before you do this, you will need to set up a command button and enter a caption into it saying "Hide all".

Once it has run, the code protects the sheet (I used it for distribution), and changes the caption to "Show all" (to allow users to modify any errors). Clicking on the button alternates between showing everything and unprotecting the sheet, and hiding everything and protecting the sheet.


Hope this helps.

Private Sub CommandButton1_Click()
'Sub rowhide()
' rowhide Macro
' Macro recorded 02/06/2006 by riaz
' first, find last cell
If CommandButton1.Caption = "Hide all" Then
ActiveSheet.Unprotect
For myrow = 16 To 416
    If ActiveSheet.Range("a" & myrow) = 0 Then
        ActiveSheet.Rows(myrow).EntireRow.Hidden = True
    End If
Next myrow
CommandButton1.Caption = "Show all"
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
ActiveSheet.Unprotect
ActiveSheet.Rows.EntireRow.Hidden = False
ActiveSheet.Columns.EntireColumn.Hidden = False
CommandButton1.Caption = "Hide all"
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

 

[Peter_SSs]

nadirv8

Here's my suggestion to try:

Sub HideRows()
Dim Row As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
For Row = 416 To 16 Step -1
If Cells(Row, 1).Value = 0 Then
Cells(Row, 1).EntireRow.Hidden = True
End If
Next Row
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub