Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formats & Hiding
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!" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formats & Hiding
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!" . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formats & Hiding
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!" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formats & Hiding
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding a row when a cell has a value | Excel Discussion (Misc queries) | |||
Hiding a cell with a formula. | Excel Worksheet Functions | |||
hiding formula in cell | Excel Discussion (Misc queries) | |||
hiding 1 cell | Excel Discussion (Misc queries) | |||
hiding a value in a single cell | Excel Worksheet Functions |