Counting Blank Rows

[nigelk]

Hi all,

I have a problem that I'm finding very difficult to solve.

I'm importing data from a website into column A on a 1 min timer.
Sometimes a row will empty, sometimes not, and I can't find a way to count the empty rows between A1 and the last non-empty cell.

The Countblank function, for example, requires a specific range, but as I don't know where the end of the range is I can't use it.

Is there a formula/function that I could use that just looks at col A and returns the last non-blank cell, whether it's in row 2 or 200?

Thank,Nigel.

 

[T. Valko]

Hi all,

I have a problem that I'm finding very difficult to solve.

I'm importing data from a website into column A on a 1 min timer.
Sometimes a row will empty, sometimes not, and I can't find a way to count the empty rows between A1 and the last non-empty cell.

The Countblank function, for example, requires a specific range, but as I don't know where the end of the range is I can't use it.

Is there a formula/function that I could use that just looks at col A and returns the last non-blank cell, whether it's in row 2 or 200?

Thank,Nigel.

The best solution depends on what type of data is entered in the range.

Is it text?
Is it numeric?
Could it be both?

From the sound of your explanation there are no formulas in the range that return formula blanks, right?

 

[nigelk]

It can be both text and numeric, but there are no formulas

Nigel.

 

[T. Valko]

It can be both text and numeric, but there are no formulas

Nigel.

One way...

Array entered**:

=SUM(IF(A2:INDEX(A2:A20,MAX(IF(A2:A20<>"",ROW(A2:A20)))-ROW(A2)+1)="",1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust for a reasonable end of range.

 

[Haseeb Avarakkan]

Or for the whole column, Try

=COUNTBLANK(A2:INDEX(A:A,MAX(LOOKUP(1E+100,CHOOSE({1,2},2,MATCH(1E+100,A:A))),LOOKUP(1E+100,CHOOSE({1,2},2,MATCH(REPT("Z",250),A:A))))))

 

[nigelk]

Thanks for your time, the formula works well if there is only 1 value in col A, but fails if there is more than 1 entry.

For example, your formula is in A1 (I've changed it to +3 to reflect the actual row number)

=SUM(IF(A2:INDEX(A2:A20,MAX(IF(A2:A20<>"",ROW(A2:A20)))-ROW(A2)+3)="",1))

I put 200 in A14 and got 14, but then if I put 150 in A6, I get a value of 13, where I was expecting 6

Nigel.

 

[nigelk]

Haseeb, I get a circular ref warning? or if entered as an array, a 0

Thanks,Nigel

 

[nigelk]

I was thinking along the lines of using "find" in vba, where it looks at col A, and finds and reports the last entry?

Will that help?

Nigel.

 

[Haseeb Avarakkan]

Haseeb, I get a circular ref warning? or if entered as an array, a 0

Thanks,Nigel

Assume your data starts on A3, in A1 enter this,

=COUNTBLANK(A3:INDEX(A3:A65536,MAX(LOOKUP(1E+100,CHOOSE({1,2},1,MATCH(1E+100,A3:A65536))),LOOKUP(1E+100,CHOOSE({1,2},1,MATCH(REPT("Z",250),A3:A65536))))))

Change the 3 red highlighted to your starting row.

 

[T. Valko]

Thanks for your time, the formula works well if there is only 1 value in col A, but fails if there is more than 1 entry.

For example, your formula is in A1 (I've changed it to +3 to reflect the actual row number)

=SUM(IF(A2:INDEX(A2:A20,MAX(IF(A2:A20<>"",ROW(A2:A20)))-ROW(A2)+3)="",1))

I put 200 in A14 and got 14, but then if I put 150 in A6, I get a value of 13, where I was expecting 6

Nigel.

Do not change that +1!

That makes an "offset correction" to the indexed range. ROW(...)+1 MUST evaluate to 1.

It works just fine for me.

If there are no entries in the range at all then you'll get an error.