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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

macajm

New Member
Joined
Oct 21, 2005
Messages
32
try this code snippet
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
 

nadirv8

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

Well-known Member
Joined
Jun 27, 2006
Messages
779
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,720
Messages
5,708,086
Members
421,545
Latest member
TWR

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
Top