VBA - how to prevent Excel to print blank pages

[absoftver]

Dear Chandoo members,

I would appreciate if someone could give me an advice how to prevent Excel to print empty pages. I have a worksheet with formulas, drop down lists and conditional formating applied on 1000 rows and I guess that is the reason why Excel prints all 1000 rows. Do you have any idea how to avoid that besides manual filtering options?

Thank you very much!

Petra

 

[Nebu]

Hi:

Click on the set print area button to to set your print area and print.
I have used the following code to set the print area dynamicaally

Sub prnt()
Application.ScreenUpdating = False

Dim rng As Range
On Error Resume Next
i& = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A1:J" & i)
 
  With ActiveSheet.PageSetup
    .PrintArea = rng.Address
    .Orientation = xlPortrait
  End With
 
Application.ScreenUpdating = True
End Sub

Thanks

 

[absoftver]

Nebu,

Perfect, exactly what I was looking for. Thank you very much for your time and help, I really appreciate it.

Petra