Count blank cells in column

[as586]

Hi there,

I am trying to count the number of blank cells in a column.

So far I have:

Sub findrange()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("h12") = Application.WorksheetFunction.CountBlank(Range("B2" & lastrow))

However this gives me an incorrect figure. The lastrow reference in the last line is where i think the problem is, but on its own it correctly selects the last cell in column B, so im not sure why the reference is wrong.

Any help would be appreciated.

 

[Joe4]

You have a typo.

Instead of:

Range("h12") = Application.WorksheetFunction.CountBlank(Range("B2" & lastrow))

try:

Range("h12") = Application.WorksheetFunction.CountBlank(Range("B2:B" & lastrow))

 

[jasonb75]

The last part should be "B2:B" & lastrow

 

[as586]

Great thanks very much for taking the time to help. Im still new to VBA as im sure is obvious.

 

[Joe4]

You are welcome.
Glad we were able to help!