Excel VBA - Counting Blank Cells

[ajlove20]

Hi,

I am filling in information on an already created worksheet that ha
about 20 blank rows and 15 columns. I only need to fill in 5 rows o
info on this particular worksheet. I also wanted to write a formul
that counts the number of blank cells there are after I have enter m
data. Is there a way that I can count the number of blank cell
without counting the cell in the extra rows.

Thanks in advance.

a

 

[Frank Kabel]

Hi
not quite sure but maybe
=SUMPRODUCT(--ISBLANK(A1:=20))

 

[Peo Sjoblom]

One possible way

=20-MAX((A1:O20<>"")*ROW(INDIRECT("1:20")))

entered with ctrl + shift & enter

where A1:O20 represent the 15 columns and 20 rows (adapt to fit accordingly)

will count the blank rows after the last entry in that particular column
that holds
the last entry

if you want the total empty rows counted from the first empty row from the
last entry

=(20-MAX((A1:O20<>"")*ROW(INDIRECT("1:20"))))*15

if you name the range "MyRange" you could use

=ROWS(MyRange)-MAX((MyRange<>"")*ROW(INDIRECT("1:"&ROWS(MyRange))))

array entered and the equivalent for the second formula

=COLUMNS(MyRange)*(ROWS(MyRange)-MAX((MyRange<>"")*ROW(INDIRECT("1:"&ROWS(My
Range)))))