Print Area to last cell with data

[Susan]

Help,

we are constantly adding new data and trying to write a macro (which I am
new to) that will set the print area automatically to the last row with data
in it. Can someone please help me.... I have no idea what i am doing and
the people i am writing this for have less knowledge.

Any help would be appreciated!

 

[Ron de Bruin]

Hi Susan

Maybe easier ?

Clear the print area and it will print all the data on the worksheet

 

[Susan]

Thank you but that wont work because there are hidden formulas in the blank
rows underneath the last entry. i need to be able to print to the last row
with data in it, which happens to be XXX in A??

i just want the rows that actually have all the completed data not the rows
with formulas in it that have not been used yet

 

[Mike H]

Susan,

Your question is short on detail so here's some options

lastrow = Cells.SpecialCells(xlLastCell).Row
This selects the last used row in the worksheet

lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
This select the last used cell in Column G

This small macro sets the print area but you must choose which 'lastrow' you
want and which column.

Sub stance()
lastrow = Cells.SpecialCells(xlLastCell).Row
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & lastrow
End Sub

Mike

 

[Susan]

Mike,

Thank you, that worked perfectly!!!

Susan

 

[Ron de Bruin]

I understand

You can do it with code
This example will search for "XXX" in "Sheet1"
And will print A1 & row of "XXX"

For testing I add
preview:=True

Delete this if you want to print


Sub PrintFromA1TillXXX()
Dim FindString As String
Dim Rng As Range

FindString = "XXX"

With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
.Range("A1" & Rng.Row).PrintOut preview:=True
Else
MsgBox "Nothing found"
End If
End With
End Sub

 

[Mike H]

Glad I could help. remember the next time you post give as much detail as
possible.

Mike

 

[jfarrug]

Help,

we are constantly adding new data and trying to write a macro (which I am
new to) that will set the print area automatically to the last row with data
in it. Can someone please help me.... I have no idea what i am doing and
the people i am writing this for have less knowledge.

Any help would be appreciated!

try the following macro strip, Of course you will have to determine
where the end of the data is located (i call it lastpos)

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(lastpos,
15)).Address
printsetup

Application.ScreenUpdating = True

Function printsetup()

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&D"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
End Function