Adding new rows to spreadsheet

[Roy]

Here's a scenario:

I have a macro that prints the range a1:c52. I then add
a few rows within this range. How do I get the macro to
adjust the print range to include the new range?

 

[Tom Ogilvy]

Range("A1").CurrentRegion.Printout

 

[Nick Hodge]

Roy

Making an assumption that the column will only extend to C the code below
finds the last row with data. Goes to row 65536 and looks up in columnA and
uses this to set the print area to that address.

You can use the relevant parts in your code

Sub setprintrange()
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row
Worksheets("Sheet1").PageSetup.PrintArea = Range("A1:C" & lLastRow).Address
End Sub

If the column differs you could use other methods in place of this. This
example from helpthat you use the Address property to return an A1-style address.

Worksheets("Sheet1").Activate
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Roy]

Thanks for your help.

-----Original Message-----
Roy

Making an assumption that the column will only extend to C the code below
finds the last row with data. Goes to row 65536 and looks up in columnA and
uses this to set the print area to that address.

You can use the relevant parts in your code

Sub setprintrange()
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row
Worksheets("Sheet1").PageSetup.PrintArea = Range("A1:C" & lLastRow).Address
End Sub

If the column differs you could use other methods in place of this. This
example from help region on Sheet1. Note
that you use the Address property to return an A1-style address.

Worksheets("Sheet1").Activate
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)





.

 

[Roy]

Thanks for your help.

-----Original Message-----
Range("A1").CurrentRegion.Printout

--
Regards,
Tom Ogilvy




.