From: Greendistantstar on 21 Mar 2010 02:37 Hi I have a worksheet I use frequently, where some cells have zero values. For presentation's sake, I hide rows where the value is zero, and this I do manually. The zero vales can and do change. How do I write a macro to hide cells with zero values? TIA GDS "Let's roll!" From: Gary''s Student on 21 Mar 2010 10:50 Give this a try: Sub HideZeroRows() Dim r As Range, nLastRow As Long, r2 As Range Dim n1 As Long, n2 As Long Dim f As WorksheetFunction Set f = Application.WorksheetFunction Set r = ActiveSheet.UsedRange nLastRow = r.Rows.Count + r.Row - 1 Cells.EntireRow.Hidden = False For i = 1 To nLastRow Set r2 = Rows(i) n1 = f.CountIf(r2, 0) + f.CountIf(r2, "") If n1 = Columns.Count Then Rows(i).Hidden = True End If Next End Sub -- Gary''s Student - gsnu201001 "Greendistantstar" wrote: > Hi > > I have a worksheet I use frequently, where some cells have zero values. > > For presentation's sake, I hide rows where the value is zero, and this I do manually. > > The zero vales can and do change. > > How do I write a macro to hide cells with zero values? > > TIA > > GDS > > > "Let's roll!" > . > From: Greendistantstar on 21 Mar 2010 11:22 Gary''s Student wrote:> Give this a try: > > Sub HideZeroRows() > Dim r As Range, nLastRow As Long, r2 As Range > Dim n1 As Long, n2 As Long > Dim f As WorksheetFunction > Set f = Application.WorksheetFunction > Set r = ActiveSheet.UsedRange > nLastRow = r.Rows.Count + r.Row - 1 > Cells.EntireRow.Hidden = False > For i = 1 To nLastRow > Set r2 = Rows(i) > n1 = f.CountIf(r2, 0) + f.CountIf(r2, "") > If n1 = Columns.Count Then > Rows(i).Hidden = True > End If > Next > End Sub Thanks. I'll trying running this later today. GDS "Let's roll!" From: minyeh on 22 Mar 2010 04:18 On Mar 21, 11:22 pm, Greendistantstar wrote:> Gary''s Student wrote: > > Give this a try: > > > Sub HideZeroRows() > > Dim r As Range, nLastRow As Long, r2 As Range > > Dim n1 As Long, n2 As Long > > Dim f As WorksheetFunction > > Set f = Application.WorksheetFunction > > Set r = ActiveSheet.UsedRange > > nLastRow = r.Rows.Count + r.Row - 1 > > Cells.EntireRow.Hidden = False > > For i = 1 To nLastRow > >     Set r2 = Rows(i) > >     n1 = f.CountIf(r2, 0) + f.CountIf(r2, "") > >     If n1 = Columns.Count Then > >            Rows(i).Hidden = True > >     End If > > Next > > End Sub > > Thanks. I'll trying running this later today. > > GDS > > "Let's roll!"- Hide quoted text - > > - Show quoted text - i'll prefer using autofilter function.  |