# Find last USED cell

#### Michael M

##### Well-known Member
Okay, it's Sunday and my brain has been frozen....
I simply want the last populated cell in a Range of cells in"E7:E64"
All cells have the formula =IF(B8="","",IF(D8<>"",E7-D8,E7+C8)) dragged down to E64, but As each line "A" to "D" is populated, so does "E"
So if I am down to say A20:D20....I want the value of E20 returned using VBA....please !

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Jaafar Tribak

##### Well-known Member
What is the value returned in the last "used" cell in E7:E64 ? is it a "" ie: a non blank ? if so, you could iterate the E7:E64 range from the bottom up until you find the first non blank cell.

#### Peter_SSs

##### MrExcel MVP, Moderator
Is this what you mean?
VBA Code:
``MsgBox Range("E7:E64").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Value``

#### Michael M

##### Well-known Member
Last used cell in E7:E64 is currently E20 and is a numeric 95251 formatted as "\$"

#### Michael M

##### Well-known Member

Hi Peter
Thats what I've been trying to do, but it keeps returning "" even though the last populated cel is in row 20
BTW it shows the last used row as E64 by changing you line to Row rather than value

#### AlphaFrog

##### MrExcel MVP
VBA Code:
``````Dim LastCell As Range
Set LastCell = Range("E7:E64").Find("*", Range("E7") , xlValues, , 1, 2)``````

Or you can find the last used cell in column A and use its row number for column E
VBA Code:
``Set LastCell = Range("E" & Range("A" & Rows.Count).End(xlUp).Row)``

Last edited:

#### Michael M

##### Well-known Member
Hi @AlphaFrog
Yep your 2nd option worked.....This has been doing my head in.....thank you...

Replies
5
Views
224
Replies
3
Views
46
Replies
2
Views
101
Replies
13
Views
373
Replies
12
Views
452

1,129,921
Messages
5,639,016
Members
417,062
Latest member
CM214

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back