count empty cells

JAYLO · Apr 10th, 2002, 01:06 PM

I'm using the following macro but it's not catching the empty cells. Any thoughts???

Sub selectusedrange3()

empties = 0
For Each y In ActiveSheet.Range("a:a")
If y.Value = " " Then
empties = empties + 1
End If
Next y
ccountmsg = "TOTAL EMPTY CELLS = : " & empties

MsgBox ccountmsg, , "EMPTY CELLS - proceed"

AJ · Apr 10th, 2002, 01:11 PM

I think if you want to count empty cells you need "", not " ". The latter would count cells with just a space in.

N.B. There is the Excel function COUNTBLANK

Al Chara · Apr 10th, 2002, 01:13 PM

Replace y.Value = " " with y.Value = ""

Apr 10th, 2002, 01:13 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Replace y.Value = " " with y.Value = "" __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 01:06 PM	#1
JAYLO Board Regular Join Date: Apr 2002 Posts: 210	I'm using the following macro but it's not catching the empty cells. Any thoughts??? Sub selectusedrange3() empties = 0 For Each y In ActiveSheet.Range("a:a") If y.Value = " " Then empties = empties + 1 End If Next y ccountmsg = "TOTAL EMPTY CELLS = : " & empties MsgBox ccountmsg, , "EMPTY CELLS - proceed"

Apr 10th, 2002, 01:11 PM	#2
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	I think if you want to count empty cells you need "", not " ". The latter would count cells with just a space in. N.B. There is the Excel function COUNTBLANK