Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Zero Rows - But Not Blank Rows
Hello,
Need to hide rows that a certain range sums to zero in, which the code below does. What it also does is hide any blank rows as well, which does not work for the intended purpose. Tried numerous attempts at [paraphrasing] .... If row = 0 hide AND If row = "" do not hide .... To no avail. Thanx in advance to anyone who can solve this. - Mike Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next With Range("e13:i600") ..EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then ..Rows(i).EntireRow.Hidden = True End If Next i End With On Error Resume Next With Range("e13:i600") ..EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = "" Then ..Rows(i).EntireRow.Hidden = False End If Next i End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Zero Rows - But Not Blank Rows
probably a better way to do this but adapting your code I think this may do
what you want: Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next Application.ScreenUpdating = False With Range("e13:i600") For i = 1 To .Rows.Count If WorksheetFunction.CountA(.Rows(i)) 0 Then If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True Else .Rows(i).EntireRow.Hidden = False End If End If Next i End With Application.ScreenUpdating = True End Sub -- JB "MikeF" wrote: Hello, Need to hide rows that a certain range sums to zero in, which the code below does. What it also does is hide any blank rows as well, which does not work for the intended purpose. Tried numerous attempts at [paraphrasing] .... If row = 0 hide AND If row = "" do not hide ... To no avail. Thanx in advance to anyone who can solve this. - Mike Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True End If Next i End With On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = "" Then .Rows(i).EntireRow.Hidden = False End If Next i End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Zero Rows - But Not Blank Rows
John, Thank you. This worked. But I think you're right, there MUST be a better way to achieve these results. Although the sheet doesn't lend well to Advanced Filter, re non-consistent field-names, shoudn't there still be a "standard" piece of code that says "look at range nn:nn, and if the sum of x thru y cells = 0, hide the entire row, and disregard any cells the sum to null" ?? But needed a quick fix, and you certainly came through. Thanx again. - Mike "MikeF" wrote: Hello, Need to hide rows that a certain range sums to zero in, which the code below does. What it also does is hide any blank rows as well, which does not work for the intended purpose. Tried numerous attempts at [paraphrasing] .... If row = 0 hide AND If row = "" do not hide ... To no avail. Thanx in advance to anyone who can solve this. - Mike Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True End If Next i End With On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = "" Then .Rows(i).EntireRow.Hidden = False End If Next i End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Zero Rows - But Not Blank Rows
Can you tell me how to solve the same issue of not hiding the totally blank
rows but using the hiderows vbe as follows: Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative! Thanks! Heidi "MikeF" wrote: Hello, Need to hide rows that a certain range sums to zero in, which the code below does. What it also does is hide any blank rows as well, which does not work for the intended purpose. Tried numerous attempts at [paraphrasing] .... If row = 0 hide AND If row = "" do not hide ... To no avail. Thanx in advance to anyone who can solve this. - Mike Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True End If Next i End With On Error Resume Next With Range("e13:i600") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = "" Then .Rows(i).EntireRow.Hidden = False End If Next i End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto hide blank rows | Excel Discussion (Misc queries) | |||
Macro to HIDE blank rows | Excel Worksheet Functions | |||
Hide Blank Rows | Excel Worksheet Functions | |||
hide blank rows | Excel Programming | |||
Hide Blank Rows | Excel Worksheet Functions |