If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|