Dynamic Print Area
I haven't tested but try replacing following line
LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row
with this line
LRow = .Cells(.Rows.Count, BldRngeWdth).End(xlUp).Row
I have removed SH. Not needed when already using With SH.
Placed . in front of Rows.Count because it belongs to SH
If above does not work then place Stop after End With and then while stopped
hover cursor over the various variables like LRow and rng.Address and check
their values.
--
Regards,
OssieMac
"FirstVette52" wrote:
I need to set the print area for an ever changing range of rows (may start at
row 200 on one execution, may start at Row 300 on the next), but no wider
than Column G. The length of the Print Area is also ever changing, therefore
I have used a formula that finds the last occupied row.
I made some changes to the .Range () and now it no longer finds the last
occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN
output is 7 rows.
Can anyone see what I'm doing wrong?
Sub Print_Preview()
Dim SH As Worksheet
Dim rng As Range
Dim LRow As Long
Dim BldRngeTop 'First cell in Print Area
Dim BldRngeWdth 'Last Column in Print Area
BldRngeTop = Range("Extract_Area_FirstCell").Address
BldRngeWdth = "G"
Set SH = ActiveSheet
With SH
LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row
Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow)
.PageSetup.PrintArea = rng.Address
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Thanks for any help you might give.
--
FirstVette52
|