I use

dim lastRow as integer
Dim lastCol as integer


lastRow =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("a:a")) '
counts all values in column A
lastCol =
Application.WorksheetFunction.CountA(ActiveSheet.R ange("1:1")) ' counts
all values in row 1

Won't work if there are gaps in your data, but I don't think any of the
other methods would either.



jaimetimbrell Wrote:
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.


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