Here are two relative-reference macros I am working with:

This one shades a spreadsheet area that I want
to make a print area from:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/29/2004 by VHACLEGalleJ
'

'
ActiveCell.Offset(0, -5).Range("A1:F1").Select
ActiveCell.Activate
Range(Selection, Selection.End(xlUp)).Select
End Sub
Sub Macro3()
'
================================

This one is what was recorded when I started
where Macro(2) left off and clicked on the
toolbar to set the print area:

' Macro3 Macro
' Macro recorded 4/29/2004 by VHACLEGalleJ
'

'
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$23"
End Sub

----------------------------------------------------------------

Assuming there is no way to define the print area
by excel assuming that the shaded area is
what I want to print,

I rigged a row calculation to occur in column H
in the row where criteria calculated "False"
=ROW(F23) producing obviously the result of 23.

In macro 3 I want that calculation of 23
to appear:

ActiveSheet.PageSetup.PrintArea = "$A$1:$F$(ROWNUMBER)"

Where ROWNUMBER = value of Cell H23

(I will have a separate macro recalculate this coordinate
every time)


---

Can any of you show me how to insert that variable row
number into a calculation like this?

Thanks

John (Memphisto!

--
Message posted from http://www.ExcelForum.com