How to Count blank Cells in a column till last used row

B

BDH-EXC

New member
Joined
Jan 4, 2013
Messages
3
Reaction score
0
Points
0
Hi There,

I have been busy with VBA today and i'm making an overview of the resultcodes in a workbook, only the amount of rows vary each day.

Is there a method to count the blank cells in a specific column to the last row instead of getting a number like 64,954?

I need to display the amount of blank cells, i display the number in a different sheet then the data is in.

The sheet with the data is called "DATATABLE" i now have Range("B40").Value = "=COUNTIF(DATATABEL!C[42],"""")" but this gives me every single blank cell until the end, i just need the blank cells till the last row.

Thanks in advance!

Gr. Bas
 
Sorry, I'm not really following what you're after here. Could you attach a copy of a stripped down workbook showing what you have on one sheet, and what you'd expect to see on another?
 
You could try this array formula

=COUNTBLANK(OFFSET($B$2,,,MAX(IF(B:B<>"",ROW(B:B)))))
 
I'm not great with VBA
But one idea struck me
xldown
row =
row = row-1
then if "" count

You can probably do the code if not I could write it with some little reserch.
 
Tangjoc said:
I'm not great with VBA
But one idea struck me
xldown
row =
row = row-1
then if "" count

You can probably do the code if not I could write it with some little reserch.
Click to expand...

You would need to do xlUP, xlDown will stop at the first blank.
 
Hi there guy's,

Thanks for the responses but i also posted it on another forum and i fixed it already, i used the following code:

Dim bottomA As Integer
bottomA = Range("AR" & Rows.Count).End(xlUp).Row
Sheets("Draaiboek Statussen").Range("B40") = Application.CountBlank(Sheets("DATATABEL").Range("AR1:AR" & Sheets("DATATABEL").Cells(Rows.Count, "AR").End(xlUp).Row))
 
Ken Puls said:
Thanks for posting back your solution. Just for info, please have a quick read of this article
Click to expand...

He Ken,

Thanks for the reply, i just read the article and i understand what you mean. I only have 2 forums where i ask VBA related questions on and i always try to check both, and also reply to both.

http://www.excelforum.com/excel-pro...till-last-used-row.html?p=3068237#post3068237 this is the other site where i posted.

Maybe someone will be interested in it.

Gr, Bas
 
You must log in or register to reply here.
Back
Top