PDA

View Full Version : Empty cell in a selection



arash
03-12-2009, 10:23 AM
Hi guys,

I have a problem when I want to check if there is an empty cell in a selection. I have some codes (below). I would greatly appreciate if someone could tell me the reason behind the errors (and hopefully the solution!).

My first try:
msgbox Worksheets(1).countblank(Range(Cells(2, 4), Cells(2, 8)))
Error: Object does not support this property or method.

I don't understand the error since if you check excel help for countblank you get:
expression.CountBlank(Arg1) where Arg1 is a range and it return value is double.

My second try:
If worksheets(1).Range("A2") Is Empty Then GoTo InvalidValue
Error 424: Object required

Excel error messages really sucks!

When I replace Empty with Nothing, it works (no errors) but does not recognise the empty cells.

If I replace it with "" then I get type mismatch error.

Cheers,

/Arash

georgiboy
03-12-2009, 10:29 AM
This would count the blanks in range A:A

MsgBox WorksheetFunction.CountBlank(Range("A:A"))

Hope this helps

Bob Phillips
03-12-2009, 10:30 AM
With Worksheets(1)

MsgBox Application.CountBlank(Range(.Cells(2, 4), .Cells(2, 8)))
End With

arash
03-12-2009, 10:34 AM
Thanks guys. Now it works. :)

Cheers,

Arash

mdmackillop
03-12-2009, 02:24 PM
To get a list
Sub ListBlanks()
Dim rng As Range
Set rng = Range(Cells(2, 4), Cells(2, 8))
MsgBox "Blank cells" & vbCr & rng.SpecialCells(xlCellTypeBlanks).Address(0, 0)
End Sub

Chris Bode
03-12-2009, 09:46 PM
Well, I suggest you a rather simple approach
to count blank cells in range (2,4) to (2,8)


Dim row As Integer, col As Integer
row = 2
col = 1

Dim cnt As Integer
cnt = 0

For col = 4 To 8
If Sheet1.Cells(row, col).Value = "" Then
cnt = cnt + 1
End If
Next

MsgBox "Blanks = " & cnt

to perform certain action if blank cell is encountered:-


Dim row As Integer, col As Integer
row = 2
col = 1

Dim cnt As Integer
cnt = 0

For col = 4 To 8
If Sheet1.Cells(row, col).Value = "" Then
'perform action
End If
Next

arash
03-13-2009, 01:26 AM
Thanks mdmackillop. Interesting solution.

Chris, to simplify the examples, I only choose few cells. I was looking for a general solution. Thanks man.

By the way, any one knows why my second solution didn't work?
If worksheets(1).Range("A2") Is Empty Then Goto InvalidValue

How can I use Empty?

Cheers

Bob Phillips
03-13-2009, 03:13 AM
If IsEmpty(worksheets(1).Range("A2").Value) Then Goto InvalidValue

arash
03-13-2009, 04:55 AM
Thanks. :)

/Arash