Hiding and unhiding Rows with zero values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,966
Office Version
  1. 2019
Platform
  1. Windows
I have a spreadsheet with three columns of data. Column A & B have text and column C has values which are pulled from another file.

I need to hide the data in the row where the value is zero. Should the value become greater than zero in another month, then the row must become unhidden.

I need the VBA code to hide the row where the value in column C is zero as well as the code to unhide the row when the value in column C becomes greater than zero

Your assistance will be most appreciated.

Howard
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim c As Range, rng
Set rng = Range("c3:c100")
For Each c In rng
If c.Value <> "" Then
c.EntireRow.Hidden = False
Else
c.EntireRow.Hidden = True
End If
Next c

End Sub
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Code:
Private Sub Worksheet_Activate()
Dim rng As Range, c As Range
Set rng = Columns(3).SpecialCells(xlCellTypeFormulas, 1)
Application.ScreenUpdating = False
For Each c In rng
    If c.Value = 0 Then
        Rows(c.Row).Hidden = True
    Else
        Rows(c.Row).Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,966
Office Version
  1. 2019
Platform
  1. Windows
Hi Steve

Thanks for the help

Much appreciated

Howard
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,966
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Krishnakumar

Thanks for the help. Code working perfectly

Howard
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,966
Office Version
  1. 2019
Platform
  1. Windows
Hi Krishnakumar

I set the value <1 , I cannot get the code to work-see code below. When I use the code beloe, all the rows are hidden


Private Sub Worksheet_Activate()
Dim rng As Range, c As Range
Set rng = Columns(3).SpecialCells(xlCellTypeFormulas, 1)
Application.ScreenUpdating = False
For Each c In rng
If c.Value = >=1 Then
Rows(c.Row).Hidden = True
Else
Rows(c.Row).Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub

Nissan UNITS SOLD DOBLO 0.27
Nissan UNITS SOLD 1400 LDV 9.09
Nissan UNIT SLD TERR/XTRAIL 1.82
Nissan UNITS SOLD 1 TON/NAV 14.36
Nissan U/SLD NISS MICRA 1.00
Nissan UNITS SOLD ALM/TIIDA 1.00
Nissan UNIT SLD PATROL/PATH 3.00
Nissan UNITS SLD NIS CSTAR 0.36
Nissan UNITS RET STRADA 9.36
Nissan RET UNITS SOLD STILO 0.09
Nissan U/S FIAT PAL/PUNTO 4.00
Nissan UNITS RT PUNTO/PANDA 4.18


Your assistance will be most appreciated

Howard
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

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