searching for empty cells in a range

Hi

Is it possible to search a range of cells for empty cells? If there are any, then I want an error to pop up.

I.e I have a range called 'Appliance Details'. I have tried the following code (like i would do for a single cell):

If Range(Appliance_Details).Value = "" Then Err = "Cell(s) in Appliance Details are not filled in. Please check and fill in where needed."

However, it doesn't work.

I don't car how many empty cells there are. All I need to know is that there is at least one empty cell so I can prompt the user to go and check the range.

I would prefer to do it by range becuase doing by individual cell would take forever and not be of massive benefit for my needs.

If I am doing this in a stupid way I am always grateful for advice.

Thanks in advance

Hi

I am trying something else. I think I have worked out how to count the number of empty cells in a range but it is not working. Below is the function I am using. I am afraid there are one or two other validation rules in there two, but I know they work. When I try to ruin this function with the cell count in it the following error comes up:

"Object variable or With block variable not set"


Sub check_cells()

Dim Err As String
Dim Length As String
Dim MaxNum As String
Dim EmpCell As Integer
Dim Holder As Range

'set the variables

MaxNum = Range("AF5").Value

Length = Len(MaxNum)

Holder = Range("B27:AG29")

EmpCell = 0

If Range("AF5").Value = "" Then Err = "You must enter the Maximo Number."
If Length < 5 Then Err = "You have not entered a valid Maximo Number"

If Range("W11").Value = "" Then Err = "You must enter the customer name and location."

'count each blank cell in a range
For Each x In Holder
If IsEmpty(x.Value) Then EmpCell = EmpCell + 1
Next x

If Answer > 0 Then Err = "There are " & EmpCell & " cells not completed"

If Err <> "" Then
MsgBox Err, vbCritical
Else
MsgBox "This sheet is ready to save and send!"
End If

End Sub

Right

What a turd,.. I forgot to put in the 'Set' bit for the range. All sorted now,

Well almost.....

The range I am looking at has 18 cells. However, these 18 cells are made up of probably over 100 cells that have been merged. When I run the code it says I have 105 cells empty. If I fill in the 18 cells I can see (the merged) then it says I still have 87 empty, which makes sense.

However, this is no good to me. Why is it counting the range as seperate cells even though I have merged them, is there anything I can do?

Thanks again!