Hi Mel, No need to select any ranges. You are not printing based on the selection, so it's irrelevant.
Here are a few things you could try: 1. Hide the rows without looping. (Beware though that any method that hides rows will force those rows and their dependencies to be recalculated in Excel 2003+.) i. You could use SpecialCells to find blanks in the first column. For example: ' This procedure operates on the active worksheet Dim HiddenRange As Range ' Dimension a new Range variable Application.EnableEvents = False ' ...and disable events Application.ScreenUpdating = False ' Hide all rows with a used cell and column "A" empty Set HiddenRange = Range("A18:A167").SpecialCells(xlCellTypeBlanks).EntireRow HiddenRange.Hidden = True ' Set Print Area (A1:P175 instead of A1:Q175 based on the description of your worksheet) ActiveSheet.PageSetup.PrintArea = "$A$1:$P$175" ' Print! ActiveSheet.PrintOut ' At End of routine, unhide HiddenRange: HiddenRange.Hidden = False ' ...and re-enable events and screen updating: Application.EnableEvents = True Application.ScreenUpdating = True ii. Another method would be to use AutoFilter instead of SpecialCells/EntireRow/Hidden (though this won't avoid recalculation of those rows and their dependencies). 2. Print the rows without hiding... i. Use Range.PrintOut (There will be a page break between each block of contiguous rows). This can replace your entire procedure (I ignored the used range this time and took your word that the range of interest is always A18:P175) ' This procedure operates on the active worksheet ' Assuming no formulas in column A, determine rows for non-blank A values: Application.Intersect( _ Application.Union( _ Range("1:17"), _ Range("A18:A167").SpecialCells(xlCellTypeConstants).EntireRow, _ Range("168:175") _ ), _ Range("A:P") _ ).PrintOut ii. Copy the desired cells to a temporary sheet. (Determine the PrintRange as above; create a temp sheet; copy values and formats to it; print the new, contiguous range, delete the sheet ---for a contiguous printout) Hope this helps. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mel Sent: Monday, March 19, 2012 2:27 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to hide rows, print, unhide rows runs very slow - how to speed up? I have a macro that will hide unused rows in a spreadsheet, print the active sheet, then unhide the rows. Spreadsheet is from cell A1 to P175. Base info is recorded in various cells on rows 1 to 4. Cells 5 to 16 display output. The main Data input starts in cell A18 to G 167. A18 is a from date, B18 to date, with comments in column P. Because I have 150 lines that could be used, I want to hide unused rows when printing to reduce the pages. Rows 168 to 175 display results and are not hidden. Here is the macro I have. It works but runs very slowly. I'm lookig for ways to speed it up. Option Explicit Sub PrintNonBlankColA() Dim RowCrnt As Integer Dim RowLast As Integer ' Note: This operates on the active worksheet Application.ScreenUpdating = False RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row ' Hide all rows with a used cell and column "A" empty For RowCrnt = 18 To RowLast If IsEmpty(Cells(RowCrnt, "A")) Then Range(RowCrnt & ":" & RowCrnt).EntireRow.Hidden = True End If Next Range("A1:Q175").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$175" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Rows("17:168").Select Selection.EntireRow.Hidden = False Range("B2").Select End Sub any suggestions to speed it up appreciated. Mel -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com