Find last USED cell

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean?
VBA Code:
MsgBox Range("E7:E64").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Value
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Last used cell in E7:E64 is currently E20 and is a numeric 95251 formatted as "$"
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 2, 2009
Messages
16,402
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:
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi @AlphaFrog
Yep your 2nd option worked.....This has been doing my head in.....thank you... (y) (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,092
Messages
5,640,066
Members
417,125
Latest member
sfreind

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
Top