Check if merged cells are blank

[leterrier]

I have an Excel 2003 spreadsheet containing a mix of UK date and currency formatted cells. I cannot change the structure of the spreadsheet. In VBA, during a worksheet change event, I want to check if the user has deleted the data from a number of ranges comprised of merged cells. If the user has deleted the data, I simply want to exit the sub. I have tried IsNull and IsEmpty but they don't work on merged cells. Using Range.value = "" throws up an application/ object related error.

In simplier terms
If a change event occurs AND the change involves a range comprised of merged cells AND the change is a deletion of data from the merged cells Then Exit the sub - my existing code covers other scenarios.

I've read forums far and wide for a simple solution (my experience is quite limited) but nothing yet does the trick. Grateful for any help. Thanks and keep up the great work!!

 

[Snakehips]

leterier,

Welcome to the board.

Try the following.

Basically, I have used the .MergeCells property and the fact that

If Target.Value > "" Then GoTo DoSomeStuff '********

will create an error if the merged cell is empty.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.MergeCells = True Then GoTo DoSomeStuff
On Error GoTo MergedAndEmpty
If Target.Value > "" Then GoTo DoSomeStuff  '********
MergedAndEmpty:
MsgBox "Merged and empty so exit sub"
On Error GoTo 0
Exit Sub
DoSomeStuff:
MsgBox "Do some stuff"
End Sub

Hope that helps.

 

[Rick Rothstein]

Here is a generic structure that may help you out...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.MergeCells Then
    ' Code involving Merged Cells goes here
    If Target(1).Value = "" Then
      ' The value for the merged cells is the empty sting ("")
      Exit Sub
    Else
      ' There is a value in the merged cells, so code to deal with it goes here
    End If
  Else
    ' Code involving non-merged cells goes here
  End If
End Sub

Note the above is not "perfect" in that it is possible for the Target to be multi-celled where it is composed of both normal cells and merged cells... that would all end up being handled by the last Else block above.

 

[leterrier]

Rick, thank you very much, I will give this a try t work on Monday and let you know how it goes!

 

[leterrier]

Snakehips, thanks very much for the reply and the welcome, I will try this at work on Monday and let you know how it goes, thanks again!

 

[Snakehips]

leterrier,

It would appear that the only material difference between Rick's solution and my own is what happens if multiple cells are selected.
As Rick states, his will not exit if a merged cell is part of the multiple selection but will proceed. Alternatively, mine will always exit if a merged cell is part of the selection.
So you have a choice.

Either way, if you need to deal with the possibility that the user might select multple cells then maybe the following revised code might help.
It uses the Selection_Change event to determine when multiple cells are selected and sets a variable (MultiCell) accordingly.
This can then be tested in the Change event code and the code can be redirected as required.

Also, your post refers to 'checking that the user has deleted the data from merged cells'.
Both original sets of code assume that if the merged cell value, after change, is "" then you want to exit. They will do this even though the original value of the merged cell may have been "". Ie cell = "" - Delete or clear contents - cell = "".
So, if you only consider a deletion to be the removal of a data with value greater than "", you could again use the Selection_Change event to determine the pre-delete value and store it in a variable (CheckIt). This can then be tested in the Change event and code redirected if required.

'Declare the test variables
Dim CheckIt
Dim MultiCells


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.MergeCells = True Then
'Can test for multi-cells and redirect code as desired.
If MultiCells = True Then MsgBox "You have multiple cells selected"
GoTo DoSomeStuff
End If

On Error GoTo MergedAndEmpty
If Target.Value > "" Then GoTo DoSomeStuff  '********
MergedAndEmpty:
'Can test for multi-cells and redirect code as desired.
If MultiCells = True Then MsgBox "You have multiple cells selected"
'Can test if previous value was > "" and redirect code if required
If CheckIt = "" Then MsgBox "Cell was empty before delete"

MsgBox "Merged and empty so exit sub"
On Error GoTo 0
Exit Sub
DoSomeStuff:
MsgBox "Do some stuff"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.Cells.Count = Target(1).MergeArea.Cells.Count Then MultiCells = True
CheckIt = Target(1).Value
End Sub

I hope that makes sense and maybe, in principle, be of value.

 

[leterrier]

Snakehips,

Thank you for taking time to reply, fortunately my knowledge level is some way below yours and as good as that code looks I'm not clever enough to make use of it, could you translate it into a formula so I might have a chance of understanding? Thanks again!