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
