How to work =row(Cell_Address) into a macro
 

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

How to work =row(Cell_Address) into a macro
 

Did you look in HELP, there is a very good example perhaps examples
for the Case statement.

Also take a look at
http://www.mvps.org/dmcritchie/excel/events.htm#case
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Memphisto! " wrote in message ...
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/

How to work =row(Cell_Address) into a macro
 

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

"Memphisto! " wrote in message
...
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/

How to work =row(Cell_Address) into a macro
 

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

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

How to work =row(Cell_Address) into a macro
 

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

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