Adjust Print Area to include 2 rows above Table_VBA

[JamesonMH]

Hi all,

I have a table and want to include the 2 rows above the table (with titles, etc.) in my print area using VBA.

[B]ActiveSheet.PageSetup.PrintArea="Tbl1[/B][[B]#All]"[/B]

I've tried quite a few variations using OFFSET, with no luck.

Any help would be appreciated.

Thanks!
James

 

[JoeMo]

Does this do what you want?

Sub SetPAtoTblPlus2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")  'Change table name to suit
Set Print_Area = tbl.Range.Offset(-2, 0).Resize(tbl.Range.Rows.Count + 2)
MsgBox Print_Area.Address
End Sub

 

[JamesonMH]

Does this do what you want?

Sub SetPAtoTblPlus2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")  'Change table name to suit
Set Print_Area = tbl.Range.Offset(-2, 0).Resize(tbl.Range.Rows.Count + 2)
MsgBox Print_Area.Address
End Sub

Thanks for your quick reply Joe. For some reason the code is including not only the 2 rows I wanted (rows 3), but also the 2 rows (rows 1&2) that I don't want. Note, my table starts in 5.

For more info, I'm using the print area so when I convert to PDF it looks proper. My PDF convert code is

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _                    "R:\Accounting\AP\Payment batch downloads\Pay summary.pdf" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                    :=False, OpenAfterPublish:=False

 

[JoeMo]

Thanks for your quick reply Joe. For some reason the code is including not only the 2 rows I wanted (rows 3), but also the 2 rows (rows 1&2) that I don't want. Note, my table starts in 5.

For more info, I'm using the print area so when I convert to PDF it looks proper. My PDF convert code is

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _                    "R:\Accounting\AP\Payment batch downloads\Pay summary.pdf" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                    :=False, OpenAfterPublish:=False

Are you exporting to PDF before you set the print area? No way I can see how you get extra rows from the bit of code I posted. Have you tried running just the code I posted w/o adapting it to your prior code?

 

[JoeMo]

Ignore post #4 - I think I understand what's happening. Try replacing the ealier code I posted with this:

Sub SetPAtoTblPlus2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")  'Change table name to suit
With ActiveSheet
    .PageSetup.PrintArea = ""
    .PageSetup.PrintArea = tbl.Range.Offset(-2, 0).Resize(tbl.Range.Rows.Count + 2).Address
End With
MsgBox Range("Print_Area").Address
End Sub

 

[JamesonMH]

Ignore post #4 - I think I understand what's happening. Try replacing the ealier code I posted with this:

Sub SetPAtoTblPlus2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")  'Change table name to suit
With ActiveSheet
    .PageSetup.PrintArea = ""
    .PageSetup.PrintArea = tbl.Range.Offset(-2, 0).Resize(tbl.Range.Rows.Count + 2).Address
End With
MsgBox Range("Print_Area").Address
End Sub

Perfect, post #5 works just as I needed. Thanks Joe!

Sorry I wasn't more clear originally on the PDF piece.

Just wondering what does the .PageSetupPrintArea = "" mean exactly? I've seen the = "" in other codes and am not sure what it's meant to do.

James

 

[JoeMo]

Perfect, post #5 works just as I needed. Thanks Joe!

Sorry I wasn't more clear originally on the PDF piece.

Just wondering what does the .PageSetupPrintArea = "" mean exactly? I've seen the = "" in other codes and am not sure what it's meant to do.

James

You are welcome - thanks for the reply.

Setting PageSetupPrintArea to "" (a zero-length string) effectively removes any residual print area address that may conflict with the new print_area you want to set based on the location of your table.

 

[JamesonMH]

You are welcome - thanks for the reply.

Setting PageSetupPrintArea to "" (a zero-length string) effectively removes any residual print area address that may conflict with the new print_area you want to set based on the location of your table.

Got it, thanks. Would you use the "" in other code or is it mainly for the PrintArea purpose?

 

[JoeMo]

Got it, thanks. Would you use the "" in other code or is it mainly for the PrintArea purpose?

It's widely used in VBA code. For example, suppose your code loops through a series of cells (say A2:A100) to test if the cells hold a date. To speed execution you might want to skip testing for a date if the cell is empty. Something like this:

Dim cel as range, Ct as long
For each cel in Range("A2:A100")
      If cel.Value <> "" then
             If Isdate(cel.value) then Ct = Ct + 1
      End If
Next cel
Msgbox "There are " & Ct & " cells in range A2:A100 that have dates in them."

 

[JamesonMH]

It's widely used in VBA code. For example, suppose your code loops through a series of cells (say A2:A100) to test if the cells hold a date. To speed execution you might want to skip testing for a date if the cell is empty. Something like this:

Dim cel as range, Ct as long
For each cel in Range("A2:A100")
      If cel.Value <> "" then
             If Isdate(cel.value) then Ct = Ct + 1
      End If
Next cel
Msgbox "There are " & Ct & " cells in range A2:A100 that have dates in them."

Makes sense, thanks for providing an example too.

I'm just starting to get my feet wet with VBA (it's pretty addicting) so I appreciate your time in helping Joe!

Cheers,
James