Jaime

If the data in R is contiguous, I use

Dim lLastRow as Long
lLastRow=Range("R65536").End(xlup).Row

You can then use the number in lLastRow in you range references

(XL97 on)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"jaimetimbrell"
wrote in message
news:jaimetimbrell.22kl8m_1138830003.5617@excelfor um-nospam.com...

Hello,

I am new to macro's and I would be grateful for any advice:

I have a macro that looks at about 100 spreadsheets and performs
various calculations, below is an example:

Range("Y5").Select
Selection.FormulaArray = _
"=IF(MIN(IF(R5C19:R6000C19=ROWS(R5C1:RC[-24]),R5C11:R6000C11))<SMALL(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11),2),MIN(IF(R5C19:R6000C19=ROWS(R5 C1:RC[-24]),R5C11:R6000C11)),"""")"

as you will see there is a constant reference to the last row (R6000) -
but I have added this manually as I know this is the last row of the
biggest spreadsheet - but there must be a way of calculating which the
last row is...but I am a bit dim and cant work out how to programme
it...

..I wonder if anyone else could help me with the code?

Thanks in advance.
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=507462