Worksheet Function to Find First Cell With Contents in a Ran

MattHyatt

New Member
Joined
Oct 13, 2002
Messages
6
I have a range A1:A100, sometimes with five rows of data, and sometimes with 95 rows of data. I know how to find the first empty row in the range using a macro (thanks to MrExcel), but I would like to get the same information with a worksheet function. Any suggestions?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

You can try something like:

=VLOOKUP("",A1:A100,1,false)
 

MattHyatt

New Member
Joined
Oct 13, 2002
Messages
6
If A1:A3 contains 1, 2, and 3, respectively, A4 is blank, and B1 contains function:

=VLOOKUP("",A1:A4,1,FALSE)

Excel returns #N/A in B1.

I also tried MATCH using "" with similar results.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-10-14 16:23, MattHyatt wrote:
I have a range A1:A100, sometimes with five rows of data, and sometimes with 95 rows of data. I know how to find the first empty row in the range using a macro (thanks to MrExcel), but I would like to get the same information with a worksheet function. Any suggestions?

Is A1:A100 the range of interest, that is, Is this range fixed?
 

MattHyatt

New Member
Joined
Oct 13, 2002
Messages
6

ADVERTISEMENT

Well, THAT'S interesting. It does work ... but WHY?
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Magic!! 9.999999999999999E+307 is the largest value that Excel can store. =MATCH(9.999999999999999E+307,A:A) "looks" for the largest value that is less than or equal to 9.99999999999999E+307, and is expecting the list to be in ascending order. Since it never finds a value that large it goes all the way to the end of the list (the last value encountered) and returns it's row number.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,530
Members
414,245
Latest member
Major Aly

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