what code to use to hide to hide rows showing 0

nadirv8

New Member
Joined
Sep 12, 2006
Messages
9
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
try this code snippet
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,487
Messages
6,130,945
Members
449,608
Latest member
jacobmudombe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top