searching merged cells

Originally Posted by chris_mayer

Hi

I am searching groups of cells to see if there are empty cells within a particular range.

However, although my code works the search counts all the cells that make up a merged cell so says that I have many empty cells when I do not!

I.e. I have 10 cells for instance, but each of those 10 cells is made up of 15 merged cells. Therefore, If my 10 cells are empty, my code reports 150 cells as been empty rather than 10. If I fill in my 10 merged cells, it reports 140 cells as been empty..

Part of my code is below. Can anyone help??

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

Set 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

Hi,

display EmpCell / 10

or do you have a mixed range search?
---

Originally Posted by chris_mayer

Hi

Sorry, only used that as an example for ease of explanation. My merged cells are made up different numbers of merged cells so that wouldn't work :-(

Aaaha - ok

You can cycle through the range of cells and test if each is a part of a Merge, as per the help on 'Merged' -
Set ma = Range("a3").MergeArea
If Range("a3").MergeCells Then
ma.Cells(1, 1).Value = "42"
End If

but you would need a serious reason to do that.

---

Originally Posted by chris_mayer

Thanks Bryan

I will have a look at what you have suggested.

Unfortunately, it is important for me to validate these cells to see if they are empty. The sheet in question is a legal document and it is very important that all cells have something entered. I don't want to have to write code for each individual cell in the sheet becuase that would mean, in my opinion a lot of code and no easy way of telling the user (excel novices) which cell they need to fill in.

The sheet is split into sections so my idea was to seach each section and bring up an error telling the user that there was x number of cells in that section that are empty.

I thought it would be simple, whoops!

Hi,

It may be, you already have a loop to count, so you could add the 'Merged' test to that and save the merged range and not count future cells in that range.

Not perfect, but depending on how you merged might be OK

---