Formula to find first blank row

achterberg

New Member
Joined
Feb 9, 2005
Messages
3
I searched the forum, but did not find a formula to do this. I need to return the row reference of the first blank row in a spreadsheet.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
achterberg said:
I searched the forum, but did not find a formula to do this. I need to return the row reference of the first blank row in a spreadsheet.

=MATCH(TRUE,ISBLANK(A1:A100),0)

which you need to confirm with control+shift+enter instead of the usual enter.
 

micarlson

New Member
Joined
Jul 30, 2003
Messages
17
I don't know that there is a formula for finding a blank row. I can however think of several ways to find it using VBA. Just in case you are interested:

x=1
Do until cells(x,1)=""
x=x+1
Loop

Or

myRow=activesheet.cells.specialcells(xlcelltypelastcell).row
myRow=myRow+1

The first example assumes data in range A1 and that there are no blank cells in the first column unless the row is blank. If this is not the case, code can be modified to point to a cell that is always populated unless the row is blank. The value of x after the loop stops is the first blank row.

The second example finds the last active row in your spreadsheet. Adding one to that number will give you the next vacant row number.

Hope that helps.

What exactly are you trying to do? I can think of several ways around this type of problem, but it depends upon what results you are expecting.
 

achterberg

New Member
Joined
Feb 9, 2005
Messages
3
First Blank Row

I am trying to find the first blank row (assuming, as you stated, that the data is in range A1 and that there are no blank cells in the first column unless the row is blank) through the last row of the spreadsheet and delete them. I have a formula for the second part already. I need a formula (not a VBA solution) since I need the row numbers (beginning and end) returned so I can incorporate it into a longer applescript that is taking a .cvs file output from a GanttProject schedule and doing magical things and then updating iCal (which is automatically syncronized on the web). Right now I have the cells hard coded, but if the schedule changes, the row numbers may change and throw the whole thing off. I would like to try to prevent that.

And I know that VBA routines can be called from applescript, except that this is broken in Office X.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Have you tried Aladin's formula? It will get you what you say you want...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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