# Returning contents of Last Used Cell in Column

#### I/O_Dork

##### Board Regular
Hi all:

I know how to write a formula to return the contents of the last used cell in a given column or range, but the issue here is that I don't just want to look for the actual contents of the last cell but the results of the formula in the last used cell.

For example, if I have a formula that occupies the cells in column R (or say out to row 500) and my last logical [true] formula result occurs on R10 not R500... I want the offset or index formula to return the last cell based on the formula result, not that fact that its the last cell that has anything in it or even a formula. How do I accomplish this. Currently, my offset formula is returning cell R500 b/c that is the last cell with content and not R189 which has a formula result of \$99,000.00. The column is formated for number(accounting) as the formulas in column A return the dollar amount invested.

Here is my "find last used cell" formula:
=OFFSET(Blotter!\$R\$1,MATCH(MAX(Blotter!\$R:\$R)+1,Blotter!\$R:\$R,1)-1,0)

Any input is much appreciated!

Brian

#### Arul.rajesh

##### Active Member
Is your data in column "R" sorted?
why do you use an approximate match?
also why did you add 1 to the maxvalue in column R when you were doing an approximate match anyway?
Does column "R" contain a combination of formulas and constants?
Do you want to find the last row in column "R" which has the formula?

#### mikerickson

##### MrExcel MVP
As I understand it, you have a formula (somewhere) and based on the results of that formula, you want to look at the "last cell based on that formula result".

Given a column, are you looking for the last cell in that column that contains a formula?
In VBA, that would be
Code:
``````With Columns(myColumn).SpecialCells(xlCellTypeFormulas)
With .Areas(.Areas.Count)
End With
End With``````

1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...