Help with: Hide empty rows, unhide others from johnske

[hockey1859]

I used the code in the article successfully; however, I found that it hides rows under .50 due to rounding. Is there a line I can put in to the code to make it display small amounts? I'm pretty new to using the VBE, so any help would be greatly appreciated.


For reference, the code i'm using is below, with the row and column references changed for my worksheet.

[VBA]
'<< Code for worksheet module >>

Option Explicit

Private Sub Worksheet_Activate() Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 4
Const LastRow As Long = 20

'< Set the columns that may contain data >
Const FirstCol As String = "B"
Const LastCol As String = "G"
'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub
[/VBA]

[johnske]

Just change the criteria for determining whether row's a blank row from Sum to CountA i.e change
[VBA]RowRangeValue = Application.Sum(RowRange.Value)[/VBA]
to [VBA]RowRangeValue = WorksheetFunction.CountA(RowRange.Value)[/VBA]

[hockey1859]

Thank you for the speedy reply. I made the change, but now it's not hiding any of the 0 value rows, any more suggestions?

Thanks in advance for the help

[johnske]

OK, try CountIf then...
[VBA]
RowRangeCount = WorksheetFunction.CountIf(RowRange, ">0")
[/VBA]

[hockey1859]

Johnske,

I was still having trouble with the

RowRangeCount = WorksheetFunction.CountIf(RowRange, ">0")

because it was saying it was undefined or something. But i went back and rewrote the formulas to give me a "" for a 0 rather than a 0, then changed the criteria to count (rather than countA so it wouldn't count the "") and now it's working. I appreciate all the help, and also thanks a lot for this wonderful code!