ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Find first empty cell in column (https://www.excelbanter.com/excel-discussion-misc-queries/247896-find-first-empty-cell-column.html)

TomHull

Find first empty cell in column
 
Hi,

I need a formula that finds the first empty cell at the bottom of a column
of data. I have tried using the find and select function in a macro and it
fails to find cells often. Have also tried using a MATCH formula but it
doesnt seem to like finding an empty cell.

Any Help,

Thanks

Tom

Bernard Liengme[_3_]

Find first empty cell in column
 
last nonblank
Non-array, non-volatile formula for contents of last, non-blank cell in a
range:

=LOOKUP(2,1/(A1:A100<""),A1:A100)

For last non-blank text

=LOOKUP(REPT("z",255),Sheet1!e1:e100,Sheet1!e1:e10 0)

You can shorten that to:

=LOOKUP(REPT("z",255),Sheet1!e1:e100)

Last non-zero
=LOOKUP(2,1/(B7:H7<0),B7:H7)

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"TomHull" wrote in message
...
Hi,

I need a formula that finds the first empty cell at the bottom of a column
of data. I have tried using the find and select function in a macro and it
fails to find cells often. Have also tried using a MATCH formula but it
doesnt seem to like finding an empty cell.

Any Help,

Thanks

Tom




Jacob Skaria

Find first empty cell in column
 
Since you have tried a macro..try the below..If the first cell is not blank
try the below....

Dim varFound As Variant
Set varFound = Columns(1).Find("", LookIn:=xlValues, lookat:=xlWhole)
MsgBox varFound.Address

If this post helps click Yes
---------------
Jacob Skaria


"TomHull" wrote:

Hi,

I need a formula that finds the first empty cell at the bottom of a column
of data. I have tried using the find and select function in a macro and it
fails to find cells often. Have also tried using a MATCH formula but it
doesnt seem to like finding an empty cell.

Any Help,

Thanks

Tom



All times are GMT +1. The time now is 02:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com