Formula to find first blank row

[achterberg]

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.

 

[Aladin Akyurek]

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]

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]

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]

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

 

[achterberg]

Yes, it does work. I was responding to micarlson's question.

 

[micarlson]

I learned something too.