How to use "Print_Area" to select my range.

[tom]

I'm currently using this structure to define my range:

Dim myRng As Range
With Worksheets("Education")
Set myRng = .Range("A4", "A74")
End With

However, the last cell in the range I need (A74 in this case), changes
depending on the size of my print area.
Is there a way to change the above so that the last cell in the range is 16
cells above the last row in my print area?

Many Thanks.

Tom Morris

 

[Jim Cone]

Tom,
Use the following function to get the last row for all print
areas on the sheet. Substract 16 from the result.
The function returns 0 if an error occurs.
'---------------------------------
Function PrintAreaLastRow(ByVal strTitle As String) As Long
On Error GoTo ThatsAll
Dim lngtemp As Long
Dim lngCount As Long
Dim rngRows As Excel.Range
Dim rngArea As Excel.Range

On Error Resume Next
Set rngRows = Range(strTitle)
If Err.Number <> 0 Then Exit Function
On Error GoTo ThatsAll
For Each rngArea In rngRows
lngCount = rngArea.Rows.Count
lngtemp = Application.Max(rngArea(lngCount, 1).Row, lngtemp)
Next
PrintAreaLastRow = lngtemp

Exit Function
ThatsAll:
Beep
PrintAreaLastRow = 0
End Function
'--
Sub CallLastRowFunction()
MsgBox PrintAreaLastRow(ActiveSheet.PageSetup.PrintArea)
End Sub
----------------
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<[email protected]>
wrote in message
I'm currently using this structure to define my range:
Dim myRng As Range
With Worksheets("Education")
Set myRng = .Range("A4", "A74")
End With
However, the last cell in the range I need (A74 in this case), changes
depending on the size of my print area.
Is there a way to change the above so that the last cell in the range is 16
cells above the last row in my print area?
Many Thanks.
Tom Morris

 

[tom]

Thanks Jim,
Now, how do I insert this result into my original structure to define my
range?

 

[Jim Cone]

This way...
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

Sub DoesMoreMoneyReallyHelp()
Dim myRng As Excel.Range
Dim lngLastRow As Long
With Worksheets("Education")
'Call function and subtract 16 from result.
lngLastRow = PrintAreaLastRow(.PageSetup.PrintArea) - 16
'Is something wrong?
If lngLastRow < 1 Then
MsgBox "Check print area. "
Exit Sub
End If
'Finally the print area - notice the dots.
'Uses the lngLastRow variable as the row number.
Set myRng = .Range(.Cells(4, 1), .Cells(lngLastRow, 1))
End With
'For information only while testing code.
MsgBox myRng.Address
Set myRng = Nothing
End Sub
'-------
Function PrintAreaLastRow(ByVal strTitle As String) As Long
'Jim Cone - San Francisco - USA, June 2006
On Error GoTo ThatsAll
Dim lngtemp As Long
Dim lngCount As Long
Dim rngRows As Excel.Range
Dim rngArea As Excel.Range
On Error Resume Next
Set rngRows = Range(strTitle)
If Err.Number <> 0 Then Exit Function
On Error GoTo ThatsAll
For Each rngArea In rngRows
lngCount = rngArea.Rows.Count
lngtemp = Application.Max(rngArea(lngCount, 1).Row, lngtemp)
Next
PrintAreaLastRow = lngtemp
Set rngRows = Nothing
Exit Function
ThatsAll:
Beep
PrintAreaLastRow = 0
End Function
'------------------------


<[email protected]>
wrote in message
Thanks Jim,
Now, how do I insert this result into my original structure to define my
range?

 

[tom]

Thank you Jim,
That works very nicely.

 

[Jim Cone]

You are welcome.
Jim Cone


<[email protected]>
wrote in message
Thank you Jim,
That works very nicely.