Printing Help
 

Hi,
I'm new to excel and have created a spreadsheet with a dropdown list in the
first column. I had a problem printing because it would print the pages even
if there wasn't any text. I either got the following code from this site or
from the contextures website. It limits the pages printed but now it always
prints out one extra page. I need it to print only the page with text. It
will be a controlled document so having it say "2 of 2" when there isn't a
2nd page is unacceptable. I'm wondering if the code can be changed so it
prints only the page with text. I'm not familar with Excel VBA code (I work
mainly in Word and am quite proficient with Word VBA code). Any help will be
greatly appreciated.
Here's the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
For r = lr To 1 Step -1
If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For
Next r
..PageSetup.PrintArea = .Rows("1:" & mr).Address
'or to limit columns
'.PageSetup.PrintArea = .Range("a2:x" & mr).Address
End With
End Sub

Thanks,
Mickey