Printing a Dynamic Range

[Stumped]

I am trying to print out a Range of Data that can change as new Rowss
are added.

Here is my Code::-

Option Explicit


Public Sub PrintData()
Dim lrow As Range
Dim rc As Integer
Dim ar As Integer
Dim Response As String

Application.ScreenUpdating = False

Sheets(1).Activate

Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)

lrow.Activate
ar = ActiveCell.row

Range(Cells(1, "a"), Cells(ar, "M")).Select ' Selects Print Area

MsgBox ("Ensure that the Printer is on!")


ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
Response = MsgBox("Print in Black and White?",
vbYesNo)
If Response = vbYes Then
.BlackAndWhite = True
Else
.BlackAndWhite = False
End If
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A5").Select

Application.ScreenUpdating = True


End Sub

This usually Prints only the Cell at A1 and goes no further. Is there
any way to make the print area dynamic?

Thanks for any Help.

 

[Roger Whitehead]

Range("PrintArea").printout

 

[Jim Cone]

Try changing this...
ar = ActiveCell.row
Range(Cells(1, "a"), Cells(ar, "M")).Select ' Selects Print Area
MsgBox ("Ensure that the Printer is on!")
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address

To...
set lrow = Range(Cells(1, "a"), lrow)
ActiveSheet.PageSetup.PrintArea = lrow.Address
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Stumped" <[email protected]>
wrote in message
I am trying to print out a Range of Data that can change as new Rowss
are added.
Here is my Code::-
Option Explicit

Public Sub PrintData()
Dim lrow As Range
Dim rc As Integer
Dim ar As Integer
Dim Response As String

Application.ScreenUpdating = False

Sheets(1).Activate

Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)

lrow.Activate
ar = ActiveCell.row

Range(Cells(1, "a"), Cells(ar, "M")).Select ' Selects Print Area
MsgBox ("Ensure that the Printer is on!")

ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
Response = MsgBox("Print in Black and White?", vbYesNo)
If Response = vbYes Then
.BlackAndWhite = True
Else
.BlackAndWhite = False
End If
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A5").Select
Application.ScreenUpdating = True
End Sub

This usually Prints only the Cell at A1 and goes no further. Is there
any way to make the print area dynamic?
Thanks for any Help.