# Worksheet Function to Find First Cell With Contents in a Ran

#### MattHyatt

##### New Member
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?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### phantom1975

##### MrExcel MVP
WELCOME TO THE BOARD!

You can try something like:

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

#### MattHyatt

##### New Member
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.

#### Mark W.

##### MrExcel MVP
=MATCH(9.99999999999999E+307,A:A) returns the last row number.

##### MrExcel MVP
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

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

which one?

##### MrExcel MVP
On 2002-10-14 17:31, MattHyatt wrote:
Well, THAT'S interesting. It does work ... but WHY?

There are a number of threads in which the big number as lookup value for MATCH is discussed (if that's what you're asking). One such a thread is

This message was edited by Aladin Akyurek on 2002-10-14 18:00

#### Mark W.

##### MrExcel MVP
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,026
Messages
5,835,024
Members
430,332
Latest member
Charly_Moon

### 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