Excel - Don't Print Blank Rows using VBA

[mizenerman]

Hello,

Im trying to set up a worksheet to exclude blank rows when printing. This is what I'm running:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

From what I see all is correct but it's not working so it's obviously not correct. Can someone please point me in the right direction? PS. Don't want to use a macro unless absolutley necessary.

Thanks!!

 

[xlhelp]

You should hide all the rows that are blank and then print

Member- AAAA Association Against Acronym Abusers

 

[mizenerman]

That's what I'm trying to avoid. This worksheet is being used by multiple users and the data is being updated constantly then printed off. Every time the worksheet changes we have to hide rows then print. In addition to that this worksheet is being auto populated by formulas from other worksheets. If someone hides the cells then saves the worksheet unintentionally problems ensue. I was trying to come up with a solution programmatically.

 

[xlhelp]

Then use something like
Sheets("Sheet1").Copy After:=Workbooks("Book2").Sheets(3)to copy the sheet and then do as suggested above

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

...or create a macro to...

save the Filter settings

filter non-empty rows

print

restore filter settings

Skip,

_[red][/red]

 

[xlhelp]

excusarme señor Vaught. Wouldn't the filter stop filtering at the blank row?

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

the OP seems concerned about hiding some format.

"...If someone hides the cells then saves the worksheet unintentionally problems ensue...."

just retoring the original filter configuration, which might probably be the ShowAllData state.

Skip,

_[red][/red]

 

[mizenerman]

Thanks to all. I'll give them both a try and see how it goes. Thanks again!

 

[xlhelp]

BTW, you can also Hide all your rows, print and unhide them all by doing something similar to:

Cells.Select
Selection.EntireRow.Hidden = False

Member- AAAA Association Against Acronym Abusers

 

[mizenerman]

In further researching this I found that the original formula I started with was correct however it was not recogizing the blank cells in column A because there were formulas in them (but no values appeared as a result of the formula). Thanks for all of your suggestions. They were most helpful.