Setting Print area to used range, VBA?

[Simon Lloyd]

Could anyone help with this? I am trying to print out a sheet setting the print area to the used range, the code i have constructed below will indeed set the print area to the used range but it sees the used range as all the cells that contain formulae where i would like it to only set the used range from A1 to the last cell that has a value in it!

Here's what i have that prints ALL the used range!


Private Sub CommandButton1_Click()
Dim myrange As String
myrange = Cells(Rows.Count, 12).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
ActiveSheet.PrintOut
End Sub
Regards,
Simon

[Charlize]

Private Sub CommandButton1_Click()
Dim myrange As String
'myrange = Cells(Rows.Count, 12).End(xlUp).Address
myrange = Range("L" & Rows.Count).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
ActiveSheet.PrintOut
End Sub

[Simon Lloyd]

Thanks for the reply charlize, but the line
myrange = Range("L" & Rows.Count).End(xlUp).Address is exactly the same as
myrange = Cells(Rows.Count, 12).End(xlUp).Addressand both give me L201 i have formulae in the cells up to L201 but only have values up to L67!

Its odd because .End(xlup) normally finds the last row of data not formula!

Regards,
Simon

[Simon Lloyd]

I solved it like this....its not ideal but does the job!

Private Sub CommandButton1_Click()
Dim myrange
Dim Rng, cell As Range
Set myrange = Range("A65536").End(xlUp)
Set Rng = Range("A25:" & myrange.Address)
For Each cell In Rng
If cell = "" Then
myrange = cell.Address
MsgBox cell.Address
ActiveSheet.PageSetup.PrintArea = "$A$1:L" & cell.Row
ActiveSheet.PrintOut
Exit Sub
End If
Next

End Sub
Regards,
Simon

[Simon Lloyd]

Just to bring this back to the forefront for today, does anyone have a smarter way than this?

Regards,
Simon,

Will mark this solved after today!

[mdmackillop]

Hi Simon,
I used DRJ's ExcelDiet KB as the basis for this. It finds the last Constant or Formula row/colum.
Sub PrintArea()
Dim CF As Long, CV As Long, RF As Long, RV As Long
Dim Col As Long, Rw As Long

With ActiveSheet
CF = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
CV = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
RF = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
RV = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Col = Application.WorksheetFunction.Max(CF, CV)
Rw = Application.WorksheetFunction.Max(RF, RV)

.PageSetup.PrintArea = "$A$1:" & Cells(Rw, Col).Address
End With
End Sub

[Simon Lloyd]

Thanks for the reply Malcom, srry for the delay in posting ...have been busy, i will give this a try today!

Regards,
Simon