This code works wonders:
Sub HideRows()
' ***********************************************************************************************
' Date: September 15, 2002
' Description: Allows a user to select a couple of cells and hides any rows in that particular row which are not used.
' ***********************************************************************************************
Range("A1").Select
On Error GoTo BYE
Count = 0
Application.ScreenUpdating = False
Application.Cursor = xlWait
' ExcelLastCell is what Excel thinks is the last cell
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it (must also copy above para for this to work)
LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
Loop
LastRowWithData = Row ' Row number
Set rng = Intersect(Rows(1), Selection.EntireColumn).EntireColumn
MySelectedColumns = rng.Address ' Returns columns contained in selection in form $D:$G or if only one $F:$F
ColonPos = InStr(MySelectedColumns, ":")
FirstSelectedColumn = Mid(MySelectedColumns, 2, ColonPos - 2)
FirstColumnNumber = Range(FirstSelectedColumn & "1").Column
LastSelectedColumn = Mid(MySelectedColumns, ColonPos + 2, Len(MySelectedColumns) - (ColonPos + 1))
LastColumnNumber = Range(LastSelectedColumn & "1").Column
NumOfSelectedCols = Val(LastColumnNumber) - (Val(FirstColumnNumber) - 1)
StartingCol = FirstColumnNumber
StartingRow = ActiveCell.Row + 1
For i = StartingRow To LastRowWithData
For j = 0 To NumOfSelectedCols - 1
Cells(i, StartingCol + j).Select
If Len(ActiveCell.Value) > 0 Then
GoTo NEXT_ROW
End If
Next
' ********** If we get here neither row had any data in that column ******
Selection.EntireRow.Hidden = True
Count = Count + 1
Application.StatusBar = " . . . . . . . . . . . . . . . . . . Hiding" & Str(Count) & " Rows which don't contain data for selected rows!"
NEXT_ROW:
Next
Application.ScreenUpdating = True
Application.Cursor = xlDefault
Cells(StartingRow, 1).Select
Application.StatusBar = ""
Range("D1").Select
BYE:
End Sub
Change the Range reference to a column where the cells will be blank in the rows you wish to hide.
