Return Print Area Range

[RyanH]

I am getting a Type Mismatch Error on the Line indicated below. I want to
return the Range of the PrintArea on Sheet1. Any one have any ideas.

Sub Macro1()

Dim myPrintArea As Variant
Dim LastRow As Long
Dim LastCol As Long

ERROR => Set myPrintArea = Sheets("Sheet1").PageSetup.PrintArea

LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Rows(myPrintArea.Columns.Count).Column

MsgBox Cells(LastRow, LastCol).Address

End Sub

Thanks in Advance.

 

[Jim Thomlinson]

The print area is stored as a locally defined named range. Try this...

Set myPrintArea = Sheet1.range("Print_Area")

Also note that if you want to you can make the print area a dynamic named
range by going into the name manager dialog and re-defining the named range
with a formula... Kinda cool...

 

[RyanH]

Is that a default name that excel uses? If so, I did not know Excel did that.

 

[Ron de Bruin]

Look out this is the default name for a English version

In Dutch for example it is
Afdrukbereik

 

[Rick Rothstein \(MVP - VB\)]

Try it like this...

Sub Macro1()

Dim myPrintArea As Variant
Dim LastRow As Long
Dim LastCol As Long
Const WS As String = "Sheet1"
If Len(Worksheets(WS).PageSetup.PrintArea) > 0 Then
Set myPrintArea = Range(WS.PageSetup.PrintArea)
LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Columns(myPrintArea.Columns.Count).Column
MsgBox Cells(LastRow, LastCol).Address
Else
MsgBox "No Print Area defined yet."
End If

End Sub


Rick

 

[RyanH]

This look beautiful! Once agian, Rick to the rescue. I'm not sure if you
tested it yet or not, but I found that I had to make this change to your code.

Set myPrintArea = Range(Sheets(WS).PageSetup.PrintArea)

Is it more efficient to WS as a Const or Dim WS as Worksheet?

Thanks
--
Cheers,
Ryan

Try it like this...

Sub Macro1()

Dim myPrintArea As Variant
Dim LastRow As Long
Dim LastCol As Long
Const WS As String = "Sheet1"
If Len(Worksheets(WS).PageSetup.PrintArea) > 0 Then
Set myPrintArea = Range(WS.PageSetup.PrintArea)
LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Columns(myPrintArea.Columns.Count).Column
MsgBox Cells(LastRow, LastCol).Address
Else
MsgBox "No Print Area defined yet."
End If

End Sub


Rick

 

[Rick Rothstein \(MVP - VB\)]

Half-tested with some last minute off-the-top-of-my-head changes.

Probably Const (I'm not completely sure for the VBA environment); however,
for the type of usage you are going to put it through, it shouldn't matter
which you use. Actually, you can turn this into a function (callable from
other macros, or, if placed in a Module, callable from as a UDF) and add a
parameter to accept the sheet name as an argument to the function... that
way, you can ask for the print area on any sheet. Something like this...

Function GetLastCellForPrintArea(SheetName As String) As String
Dim myPrintArea As Variant
Dim LastRow As Long
Dim LastCol As Long
If Len(Worksheets(SheetName).PageSetup.PrintArea) > 0 Then
Set myPrintArea = Range(Sheets(SheetName).PageSetup.PrintArea)
LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Columns(myPrintArea.Columns.Count).Column
GetLastCellForPrintArea = Cells(LastRow, LastCol).Address
Else
GetLastCellForPrintArea = "No Print Area defined."
End If
End Function

Rick

This look beautiful! Once agian, Rick to the rescue. I'm not sure if you
tested it yet or not, but I found that I had to make this change to your
code.

Set myPrintArea = Range(Sheets(WS).PageSetup.PrintArea)

Is it more efficient to WS as a Const or Dim WS as Worksheet?

Thanks

 

[Rick Rothstein \(MVP - VB\)]

Maybe this way is more flexible... the argument is optional... if you omit
it, the active sheet will be used by default.

Function GetLastCellForPrintArea(Optional SheetName As String) As String
Dim myPrintArea As Variant
Dim LastRow As Long
Dim LastCol As Long
If Len(SheetName) = 0 Then SheetName = ActiveSheet.Name
If Len(Worksheets(SheetName).PageSetup.PrintArea) > 0 Then
Set myPrintArea = Range(Sheets(SheetName).PageSetup.PrintArea)
LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Columns(myPrintArea.Columns.Count).Column
GetLastCellForPrintArea = Cells(LastRow, LastCol).Address
Else
GetLastCellForPrintArea = "No Print Area defined."
End If
End Function

Rick