How to work =row(Cell_Address) into a macro

M

Memphisto!

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!
 
J

Jeff Standen

I think you want this:

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

Although there is probably an easier way to do this - since you have to
select the area first, you could use

ActiveSheet.PageSetup.PrintArea = Selection.Address

Violin! As the French might say.

Jeff
 
M

Memphisto!

Mr. McRitchie, thanks for responding and trying to get me going with th
Case statements. I was not able to figure that out though, havin
almost no programming experience
 
M

Memphisto!

Jeff, thanks very much. That approach worked. I'll save your messag
in my Excel Information file .

Why, do the French say "violins?"
I'm stumped.

Thanks - John Gallett
 
Top