Solved: Hiding Unused Rows

[Opv]

I came across the code below on this forum. It is designed to hide blank rows within a defined range of rows and columns. Is there any way to expand this code to hide only those rows that are actually blank and leave visible cells that are blank as a result of a formula?

[VBA]
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]

[Bob Phillips]

[vba]

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.CountIf(RowRange, "<>")

Rows(HiddenRow).Hidden = (RowRangeValue = 0)
Next HiddenRow

Application.ScreenUpdating = True

End Sub
[/vba]

[mdmackillop]

If you only need one column try
[VBA]
Sub HideBlanks()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

[/VBA]

[Opv]

Thanks, guys. I appreciate the help!

Opv