Trying to hide all rows where no data is in columns AQ-AV

[tahrah]

I'm trying to hide all rows where no data is in columns AQ-AV then
print remaining rows ONLY. Here's what I came up with but it's not
working. It is printing Row 4 only which is the column titles. There
IS data in several of the cells AQ-AV so they shouldn't have hidden
themselves but it appears that ALL rows have hidden. I appreciate any
help to figure this out.

Sub Print_6_Month_Forecast()
Dim lngRow As Long
Rows("1:3").EntireRow.Hidden = True
Columns("A:C").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:O").EntireColumn.Hidden = True
Columns("R:R").EntireColumn.Hidden = True
Columns("T:V").EntireColumn.Hidden = True
Columns("X:AP").EntireColumn.Hidden = True
Columns("AW:BC").EntireColumn.Hidden = True
Rows("A5:IV1999").Sort Key1:=Range("AQ5"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
lngRow = Range("AQ5:AV1999").End(xlUp).Row + 1
Rows(lngRow & ":1999").EntireRow.Hidden = True
Rows("A5:IV1999").Sort Key1:=Range("E5"), Order1:=xlAscending,
Key2:=Range("J5"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

ActiveSheet.PageSetup.PrintArea = Rows("4:" & lngRow - 1).Address '?
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "2007 Forecast 6-Months Rolling"
.RightHeader = "&D, &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("A5:IV1999").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending,
Key2:=Range("A5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("A4").Select
End Sub

 

[Ron de Bruin]

Hi tahrah

Here is a example from my site that you can try
http://www.rondebruin.nl/print.htm#Hide

**Hide Empty rows, Print and unhide the rows**

This example will loop through row 1:30 in "Sheet1"
If every cell in column A:G is empty it will hide that row.
After the loop it print the sheet and then unhide the rows.

You can also use this with non contiguous ranges Range("B1,D1:G1")
If the cells in column B and D:G are empty it will hide that row.


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 1 To 30
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:G1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[tahrah]

Ron - WORKS GREAT! Thank you very much. Now I'm finally done with
these macros for a while (well atleast a day or two). LOL It's funny
but as soon as management sees you can do one set of reports, they
quickly dream up a dozen more. hehehehe

Take Care,
Tahrah