Hide Empty Rows

jzsolt · July 16th, 2010, 03:09 PM

If someone could help me out, I would greatly appreciate it. I am trying to hide rows with a 0 value in a list. I would prefer to do it dynamicaly without VBA but I don't think it's possible. So what I have in a macro so far is below and it works fine except it hides fractions and decimals as well. I would need it to show the row even if the value is 0.3 or 1/3. I am baffled as to why it hides it. Thanks in advance to anyone who can help.

Dim HiddenRow&, RowRange As Range, RowRangeValue&

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

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

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns C to D 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

Shasur · July 16th, 2010, 10:44 PM

Hi

Try declaring the following

Dim RowRangeValue As Double

also you can convert the values to double before cheching

RowRangeValue = WorksheetFunction.Sum(Val(Range(FirstCol & HiddenRow).Value), Val(Range(LastCol & HiddenRow).Value))

Cheers
Shasur

jzsolt · July 17th, 2010, 03:07 PM

Hello,

Thank you, it worked

allenm · August 17th, 2010, 06:07 PM

I'd do that a bit differently, myself.

This is how I would code it:

Code:

Public Function SumsOverZero(wsSource As Worksheet, iColNumberToCheck As Long) As Boolean

'Returns True if column contains any values and those values are > 0
  Dim wsf As WorksheetFunction, bIsZero
  Set wsf = Excel.Application.WorksheetFunction
  SumsOverZero = wsf.CountIf(wsSource.Columns(iColNumberToCheck), ">0") > 0

End Function

Public Sub HideZeroColumns()

'Hides columns with no values over 0 for columns A through E
  Dim wsSheet As Worksheet, rWholeRange As Range, rThisCol As Range
  Set wsSheet = ActiveSheet
  Set rWholeRange = wsSheet.Range("A1:G1") 'Use only 1 row to limit to 1 itteration for each column
  For Each rThisCol In rWholeRange
    wsSheet.Columns(rThisCol.Column).Hidden = Not SumsOverZero(wsSheet, rThisCol.Column)
  Next

End Sub