I have a multi page spreadsheet, and I would like to have the macro record an
area that changes its number of rows constantly.

What I am getting back in the macro is an specific range of cells from the
table array sheet no matter how many rows it contains, and I use the END DOWN
to assign the range.

The lookup value on the main page records as END+DOWN, but seems to not
carry this feature to the other pages in the workbook.

Am using winXP with Office 2003.

You can mimic the end down keystrokes with the End method of the range
object

e.g Range(Cells(1,1),Cells(Cells(65536,1).end(xlup).ro w,1)).select

selects all the cells in column 1 from first to last occupied.

You might also try using Cells(1,1).CurrentRegion to return the range
that is equivalent to hitting the control-shift-8 key combination.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=542637

Hi
There are a number of ways to deal with this

I'm assuming your data is contiguous and starts in cell A1

range("a1").currentregion.select - selects all your cells that are
contiguous to A1

Range("A1:F" & Cells(1, 1).End(xlDown).Row).Select - selects the range of
cells from A1 to col F & the equivalent of using END DOWN to find the last
row from A1, assumes no gaps

You could do the same thing starting from the bottom if there are gaps in
column A, ie
Range("A1:F" & Cells(1, 65536).End(xlup).Row).Select

And I'm sure I could go on!

HTH

"Brian William Johnston" <Brian William
wrote in message ...
I have a multi page spreadsheet, and I would like to have the macro record
an
area that changes its number of rows constantly.

What I am getting back in the macro is an specific range of cells from the
table array sheet no matter how many rows it contains, and I use the END
DOWN
to assign the range.

The lookup value on the main page records as END+DOWN, but seems to not
carry this feature to the other pages in the workbook.

Am using winXP with Office 2003.

Loomah,

Cells are contiguous, however they are on another page.

The "Range(Selection,Selection.End(xlDown)) .select works fine on the page
where the macro is being recorded, however it still returns absolute
addresses on the othe pages in this Excel file.

Below is example of what the macro returns. I am not sure how to modify the
code to force the End.Down or End.Left parameters.

Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Clients!R1C1:R36174C7,5,FALSE)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],Clients!R1C1:R36174C7,6,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Clients!R2C1:R36174C7,7,FALSE)"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Offset!R2C1:R25C2,2,FALSE)"
Range("P2:S2").Select
Selection.Copy
Range("P3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("P1:S1").Select
Range(Selection, Selection.End(xlDown)).Select

As you can see (Selection, Selection.End(xlDown)).Select
works fine on the main sheet in a multi page file, but when accessing the
Clients of Offset, this feature is lost.

Are you aware of any serious tutorials dealing with this subject.

BWJ


"Loomah" wrote:

Hi
There are a number of ways to deal with this

I'm assuming your data is contiguous and starts in cell A1

range("a1").currentregion.select - selects all your cells that are
contiguous to A1

Range("A1:F" & Cells(1, 1).End(xlDown).Row).Select - selects the range of
cells from A1 to col F & the equivalent of using END DOWN to find the last
row from A1, assumes no gaps

You could do the same thing starting from the bottom if there are gaps in
column A, ie
Range("A1:F" & Cells(1, 65536).End(xlup).Row).Select

And I'm sure I could go on!

HTH

"Brian William Johnston" <Brian William
wrote in message ...
I have a multi page spreadsheet, and I would like to have the macro record
an
area that changes its number of rows constantly.

What I am getting back in the macro is an specific range of cells from the
table array sheet no matter how many rows it contains, and I use the END
DOWN
to assign the range.

The lookup value on the main page records as END+DOWN, but seems to not
carry this feature to the other pages in the workbook.

Am using winXP with Office 2003.