Set Print Area for changing ranges on a sheet

[amartin575]

Hello,

I have the following code to set the print area on a tab. I thought I had it so that it will find the last used row but it is still setting up with extra blank pages. The data on the tab can change monthly but the columns will remain the same. Any help?

Please Login or Register  to view this content.

Public Sub Workbook_BeforePrint2()
Dim lastrow As Long
lastrow = Worksheets("By Mall - In Place").Range("AD1048576").End(xlUp).Row
Dim rng As Range
Set rng = Worksheets("By Mall - In Place").Range("AD1:AS" & lastrow)
Worksheets("By Mall - In Place").PageSetup.PrintArea = rng
ActiveWindow.ActiveSheet.PrintPreview
End Sub

Please Login or Register  to view this content.

[xladept]

Maybe:

Please Login or Register  to view this content.

[amartin575]

Thanks xladept but that did not work. The problem is there are formulas that go beyond the data that is showing values. So there are blank cells but they have formulas in them. The macro is thinking those blank rows should be part of the print range and I do not want them to be. Any suggestions for that?

[xladept]

Hi A,

This will let you know if there is a formula in a specific cell - maybe you can adapt it:

Please Login or Register  to view this content.