Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to hide all rows where no data is in columns AQ-AV
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to hide all rows where no data is in columns AQ-AV
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tahrah" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to hide all rows where no data is in columns AQ-AV
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 Ron de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tahrah" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Hide columns but not the top rows | Excel Discussion (Misc queries) | |||
Hide columns and rows | Excel Worksheet Functions | |||
Auto Hide Columns & Rows | Excel Discussion (Misc queries) | |||
Hide Rows and Columns | Excel Programming |