Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Count blank cell in column until it get to a cell with value

0

Good day all,

Each cell in the column represents a specific day. Most of the cells in the column are blank. I want to count how many blank cells there are in the column until one cell in the column gets a value. I am using it on different products.

I hope someone can help me. Thank you.

excel
posted by: Man On Fire (rep: 4) on Fri Jun 12, 2026 at 9:22 am
Answer
Discuss

Discussion

Hello Man On Fire and welcome back,

This should be easy enough, just need some clarity.
Let's say in any particular column rows 1 thru 4 have a value, rows 5 thru 10 are blank, rows 11 thru 20 have a value, rows 21 thru 24 are blank and row 25 has a value.
Are you wanting to know how many blank cells are in the column for row 1 to row 25?
Or are you wanting to know how many blanks are between rows 4 and 11, rows 20 and 25? 
Both are possible but are handled differently.
Also, how do you want this result displayed - in a cell on the worksheet or as a pop-up message?
Let us know which you are looking to achievve.

Cheers   :-)
WillieD24 (rep: 738) Jun 12, '26 at 10:42 am
Hey WillieD24,

Thank you.
Column A - row 1 to 7 is blank, row 8 has value, row 9 & 10 is blank and row 11 has value. For column A, I want the formula to return 7 because the first 7 rows are empty. Column B - row 1 to 4 has value, row 5 is blank, row 6 to 10 has value and row 11 is blank. For column B, I want the formula to return 0 because the first row has value. The formula must stop when it reaches the first row that has a value. I need to do this with a few hundred columns, and it would be time consuming to manually do each column.

Man On Fire
Man On Fire (rep: 4) Jun 13, '26 at 3:23 pm
@Man On Fire
Thanks for the info.
So if I understand you correctly, you are only interested in the number of blank (empty) cells starting at Row 1 until there is data.
Will blank cells only be at the top of the column?
Is the a row which will have data in all cells in the row?

You haven't mentioned how you want to receive this info. Via a pop-up message box or stored somewhere on the worksheet.

Let me know and then I will start working on a solution for you.

And thanks for putting more trust in humans than AI   ;-)

Cheers   :-)
WillieD24 (rep: 738) Jun 13, '26 at 4:26 pm
Thank you.
Yes, I am only interested in the number of blank cells starting at row 1 until there is data. There might be blank cells lower down the column.
If the column row starts from A2 to A100, I want the result be dispalyed in A1. See image below.

I hope this helps. Thank you.
Man On Fire (rep: 4) Jun 14, '26 at 11:56 am
Thank you,
Is it possible to do it without macros, the macros on the file is blocked and I can't use the file. I will need admin rights from my employer to unblock.

I use this code 
=INDEX(IS4:IS53;MATCH(TRUE;ISNUMBER(1/IS4:IS53);0))
to select the first value in the column and display it. How can I modify this code to count the black cells until the first number or do you have an alternative for me.

Thank you.
Man On Fire (rep: 4) Jun 15, '26 at 3:17 pm
To unblock the macros yourself, you can try this. Go to the folder where the file is downloaded, right click on the file name and select properties. Then at th e bottom of the new window check the box "Unblock". If your employer doesn't have a higher lever block, this should work.

As for your formula, what cell is it in? Do your columns of data start in Row 1 or something else?
As for a formula to count the blanks at the top I will work on that and let you know.
WillieD24 (rep: 738) Jun 15, '26 at 4:21 pm
@Man On Fire
It's been a week since I gave you a way to unblock macros, plus a worksheet formula solution. I would appreciate some feedback if either solution meets your needs. 
The file attached in my answer has been updated to provide some clarity to the worksheet formula method.
WillieD24 (rep: 738) Jun 20, '26 at 10:49 am
Add to Discussion

Answers

1
Selected Answer

Hi Man On Fire,

In the attached file (Count Blanks Rev1) I have used VBA to achieve your goal. There are 2 options (Log V1 and Log V2)for you. Each worksheet has the same simple 6-column, 24-row table that I use to demonstrate the results.

On worksheet "Log V1" a separate table is created to list how many blanks are in each column.

On worksheet "Log V2" the number of blanks is entered into the cell in Row 1 of each column.

Cell references in both macros will need to be modified to suit your file.

If this solves things for you then please mark my answer as Selected.

Cheers   :-)

Update - June 15/26

I tried using the formula you posted but kept getting an error. So I built my own array formula. An array formula is typed like any other formula but must be entered using the key combination: Ctrl+Shift+Enter:

Here is the formula as typed in (don't type the curly brackets):

=(MATCH(FALSE,ISBLANK(A1:A24),0))-1

Here is the formula after entering it as an array formula using Control-Shift-Enter key combination (notice the curly brackets got added)

{=(MATCH(FALSE,ISBLANK(A1:A24),0))-1}

You just need to update the range reference (A1:A24) to suit your file.

After entering just drag it to the right and the column reference will update accordingly.

I have updated my file to include this formula on the "Log V1" sheet.

I hope this solves things for you.

Updated June 27 - file added - 2026-06-27 Sample Rev1

Worksheet "My Tests" has the formula in Row 1

Updated July 1 - file added - 2026-06-29 Sample Rev2

Worksheet "Sample 1 Revised" uses VBA

It's also interesting that your latest file contains macros yet at the outset of this you  stated that your company blocks macros.

posted by: WillieD24 (rep: 738) on Sun Jun 14, 2026 at 1:52 pm
Discuss

Discussion

@Willie - one minor point, the key combination Crrl+Shift+Enter is needed to enter array formulae in versions up to Excel 2019 but it is NOT necessary in more recent versions (365., 2021 and later) where array formulae are native and Enter alone works. 
John_Ru (rep: 6802) Jun 16, '26 at 3:12 am
Thank you very much.

It is working like I need it to work.
Man On Fire (rep: 4) Jun 23, '26 at 2:07 am
@Man On Fire,

Glad I was able to help and thank you for selecting my answer.
Just curious, which method works for you, the macro unblocking or the worksheet formula?

Cheers   :-)
WillieD24 (rep: 738) Jun 23, '26 at 10:02 am
Hey,
The worksheet answer works for me. Thank you.
Man On Fire (rep: 4) Jun 24, '26 at 3:58 pm
Man On Fire,

Thanks.

Also, your profile doesn't mention which version of Excel you are using but I suspect the formula posted is not compatble with 2016, which is what I use, and that is why I was getting errors.

Cheers   :-)
WillieD24 (rep: 738) Jun 24, '26 at 8:32 pm
Hey
When I tested the formula in the document you provided, it was working fine, but when I copy the formula to my document, it doesn't work. 
Man On Fire (rep: 4) Jun 25, '26 at 1:25 pm
@Man On Fire,

Did you use the key combination of Control+Shift+Enter to enter the formula. If you just pressed Enter that would be the cause because it is an array formula.

Without a copy of your file (with no sensitive information) I won't be able to determine why the formula isn't working in your file. Just upload a copy to your original post. It doesn't need to be the complete file; just enough to illustrate the layout and where you want the formula to reside.
WillieD24 (rep: 738) Jun 26, '26 at 11:26 am
Hey,

I have tried it with the key combination Control+Shift+Enter and without, it is not working. It is working only in the spreadsheet you provided. Thank you.

How can I send you a copy of the file (with no sensitive information). Thank you.
Man On Fire (rep: 4) Jun 27, '26 at 11:06 am
@Man Of War
1) create a copy of your file; this is important - don't create a new file to mimic your file.
2) in this copy delete all columns except maybe 6 or so.
3) in the remaing columns delete all except maybe 25 rows of data.
4) change the remaining data to something meaningless; be careful not to delete any existing formulas.
5) save this copy with a new name (ex. sample data, smple file, etc)
6) go back to your original post and click on "Edit" in bottom right.
7) at the bottom there is a place to add up to 3 files; click on the top one and browse to your file the click "Open" to add it; then click "Submit"; the file is now accessable to all teachexcel members.

Cheers   :-)
WillieD24 (rep: 738) Jun 27, '26 at 12:20 pm
Thank you,

I did that just now.
Man On Fire (rep: 4) Jun 27, '26 at 1:08 pm
@Man On Fire,

I tried entering the formula (C+S+E) on sheets Sample 1 and Sample 2 of your file and it failed to give the proper result. I then added a sheet ("My Tests") to the file you uploaded. I then added a value in assorted cells in Col's A thru M. I then typed and entered the formula (C+S+E)  in cell A1 and copied (dragged) it across to M1. As you can see on the sheet "My Tests" the formula works. I have added this modified file to my original answer.
Because the formula works on "My Tests" sheet in your workbook I can only assume there must be something amiss with the formating of your Sample 1 and Sample 2 sheets causing the formula to not work. (something I'm not going to spend time on)

Things to keep in mind:
1) Copying the formula must be done from the formula bar - not the cell.
2) pasting the formula must be to the formula bar and then entered using the C+S+E key combination. If you paste it into a cell it will not work.
3) If you edit the formula (change the range reference) you must enter the change using C+S+E. If you just press enter the formula will not work.


Hope this helps

Cheers   :-)
WillieD24 (rep: 738) Jun 27, '26 at 9:43 pm
Hey,

I tried it your way and it still doesn't work.
I found the problem is that the range the formula is checking, it all has formulas in it. When I delete my formula and just type in data randomly, then you formula is working.

The formula I use to check for activity is a Countif nested in an If statement 
=IF(COUNTIF(C4:I4;1)>0;COUNTIF(C4:I4;1);"")
. Is it possible that your formula is counting my formula. Thank you.
Man On Fire (rep: 4) Jun 28, '26 at 2:42 pm
@Man On Fire,

The formulas you post use a semicolon instead of a comma for a delimiter which indicates there is a difference in the regional settings of our systems. I suspect if you want to type a number with a decimal, your system will use a comma instead of a dot You can Google on how to change this if your boss will allow you to do so. 

You first posted that you are using a formula which begins "=INDEX(" . Now you say you are using a formula which begins "=IF(COUNTIF(". In the file you posted neither formula is in it. It's difficult to provide a solution if you keep changing the game. 

As for a formula counting a formula, yes, definitely. A cell with a formula is not empty / blank. 

To be able to help you further I will need a sample file of your file. The file should be a copy of the original file with all but about 6 or so columns deleted, all but 20 or so rows deleted, then the remaining data changed to something non-specific (numbers changed to letters, names changed to numbers, etc). It also needs to include the formula(s) you are using. Without this sample file I won't be able to help you further. This is what I asked for before but your original file was not this. Also, if you want a count of the number of empty/blank cells at the top of a column the range reference needs to that of a single column and not multiple columns as shown in your formula (C4:I4).

Cheers   :-)
WillieD24 (rep: 738) Jun 28, '26 at 5:49 pm
Thank you.
I have added another document. This is the closet I can come to what we are doing and the formulas we are using. I hope this helps.
Just to note, the INDEX formula that I have mentioned was used in another sheet that is not related to this one.
Thank you.
Man On Fire (rep: 4) Jun 29, '26 at 4:18 pm
@Man On Fire

Thank you for the new file, it better shows the structure of your worksheet. 

In the new file you have uploaded, Row 1 is where you want the formula to return the number of blank (empty) cells in each column from Row 3 to 35.In the table range I3:AB32. Therein lies the problem - every cell in that range has a formula in it and therefore is not blank / empty. Some cells appear to be empty only because the If False element of the IF formula says to put "" (nothing) in the cell, but there is still the formula so the cell is not empty / blank. As a result Row 1 shows 0 because there are no blank cells at the top from Row 3 to Row 35. The formulas in those cells (I3:AB32) are calculating as expected. 

I will give it some thought but I think the only way to count the "blank" cells is by using VBA.

Talk to your manager and see if he will allow macros for this file.

Cheers   :-)
WillieD24 (rep: 738) Jun 29, '26 at 5:38 pm
@Willie

I'm not really following this thread (or the Forum itself these days, sadly) but you say above:

"I will give it some thought but I think the only way to count the "blank" cells is by using VBA.". This may help...

Say you have data/ formulae in cells D1:D10 and want to count those which are empty or show a blank result, you might try:

=COUNTIF(D1:D10,"")


If a cell in that range has a simple formula e.g. 

=IF(D4>D5, "bigger","")

but D4 is smaller (so the text is blank), the count is unaffected.

As I say, there may be nuances that mean this can't work here.

John
John_Ru (rep: 6802) Jul 4, '26 at 12:21 pm
@John_Ru
Thanks for your insights as always.
I cover this in my discussion point of June 29, 5:38 pm (above yours)

Some cells appear to be empty only because the "If False" elemnett of the "IF" formula says to put " " (nothing) in the cell, but there is still the formula so the cell is not empty / blank.

Man On Fire wants a count of the cells at the top of each column which "appear" empty (blank). In my file "2026-06-29 Sample Rev2" solves this with VBA. Unfortunately it seems he has left this discussion.

It's disappointing the traffic on the forum has dropped of.

Cheers   :-)
WillieD24 (rep: 738) Jul 4, '26 at 7:19 pm
@Willie   My suggestion DOES record the number of cells that "appear" to be blank.

I set up D2:D10 (now avoiding the top row) with 4 blanks (D8 being blank, ll without the formula). The COUNTIF formula in D1 gave 4 blanks, When D8 had the IF formula, the count is 3 if D8 shows "bigger" but the same 4 if D4<=D5 (and D8 "appears" blank). 

That seems to give the (now absent) user what he wants but without VBA.

Agreed that it's a shame the Forum has all but died. As Don said, the widespread use of AI has decimated many such interactive sites.
John_Ru (rep: 6802) Jul 5, '26 at 6:48 am
Hey Willie & John_Ru Thank you all. I am still here. The documents I post here I recreate the formulas on my personal laptop, that’s why there might be macros on it, but not intentional. My employer won't allow macros on his system. I have checked your solution with the macros on my laptop and, it’s working fine. But I need a solution that I can recreate on my employer’s system. I tried the =COUNTIF(D1:D10,"") solution, but it count all the empty cells and don’t stop at the first one that have value.   Thank you.
Man On Fire (rep: 4) Jul 5, '26 at 7:25 am
@John_Ru,

Your suggestion counts all "blank" cells in the column provided they don't have a formula. Man On Fire just wants a count of "visually blank" cells above the first cell with data/value. Example: each cell has a formula which returns the following: D1 is " ", D2 is " ", D3 is " ", D4 is 24, D5 is  " ", D6  is 12, D7 is " ", D8 is 52, D9 is " ", D10 is " ". Your formula [=COUNTIF(D1:D10," ")] will givve a result of 7 (D1, D2, D3, D5, D7, D9, & D10);  but Man On Fire is wanting a worksheet formula which will give a result of 3 (D1, D2, & D3).
I have not been able to come up with a worksheet formula that will do this, only VBA.

Cheers
WillieD24 (rep: 738) Jul 5, '26 at 10:49 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login