Find last USED cell

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,821
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
Is this what you mean?
VBA Code:
MsgBox Range("E7:E64").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Value
 
Upvote 0
Last used cell in E7:E64 is currently E20 and is a numeric 95251 formatted as "$"
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Solution
Hi @AlphaFrog
Yep your 2nd option worked.....This has been doing my head in.....thank you... (y) (y)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top