Merged cell Dilemma, lost functionality

[RobertBon]

Hello,

Dilemma:

I feel like I have lost some functionality due to merged cells. For a worksheet change event I have the following:

ElseIf Target.Value = "" Then ... blah blah blah

For a normal cell, every time a cell is deleted (in the appropriate target zone) then it runs the code I have in the subsequent lines

Except it doesn't work for merged cells, unless I manually go into the cell, backspace all of its contents and then press enter

Does anyone know of a way to get around this.

 

[TinaP]

Macros don't like merged cells. Sometimes using "Center across selection" gives the image of merged cells without the headaches when using macros. It's on the alignment tab of the formatting dialog box. (under Text Alignment, Horizontal)

I hope this helps.

 

[RobertBon]

I didn't make the spreadsheet, the merged cells have to stay unfortunately, there's no other way to delete a cell and have condition run?

 

[TinaP]

I was afraid you wouldn't be able to unmerge the cells...

There's usually a way around the problem, but you'll have to get creative with it. Can you post your code? It would help if I could tell what line is giving the problem and which cells are merged, also.

 

[RobertBon]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("B:B")
' Only look at single cell changes
If Target.count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target = Range("B6") Then

If Target.Value = "" Then PROBLEM HERE
ActiveCell.Range("A1:R2").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("T6").End(xlDown).Delete
ActiveCell.Select

Else
Target.Offset(0, 18).Value = 1

End If

ElseIf Target.Value = "" Then AND PROBLEM HERE

Target.Range("A1:R2").Select
ActiveCell.Range("A1:R2").Select 'choose which range of cells(with respect to the active cell) to be selected

'prevents program from re-executing every time there is a change
Application.EnableEvents = False

Selection.Delete Shift:=xlUp 'move the selected cells up
ActiveSheet.Range("a1").End(xlDown).Delete
ActiveCell.Select

Application.EnableEvents = True

Else
Target.Offset(0, 18).Value = Target.Offset(-2, 18).Value + 1

End If

End Sub

Hope this helps

 

[TinaP]

Which cells are merged?

And what is the target address?

 

[RobertBon]

The Target range is column B, after and including "B6", and coincidentally these are also the merged cells.

As for the target address, the code is meant to run if there is a change made to any of the cells in the column, so no address is specified.

You think I should specify the address?

 

[TinaP]

When a merged cell is changed, it exits at

  ' Only look at that range
  If Intersect(Target, rng) Is Nothing Then Exit Sub

So that could be a problem.

Also, I'm not sure if the merged cells are horizontal or vertical -- Is B6 merged with B7, with C6 or with a mess of cells in a block? Please give me the full range of cells that are merged with B6.

 

[RobertBon]

b6 is merged with b7, b8 is merged with b9, etc...

And it's the condition: if target.value = "" that isn't reading. All other conditions under the target range are reading and running fine.

 

[TinaP]

I'm sorry it took so long to get back to you, things got busy.

I set up my worksheet just as you described in the previous post and copied the code into the worksheet. When I add data to column B, it runs beautifully. However, when I clear data from column B, it gets to

If Target.Count > 1 Then Exit Sub

and exits the sub; it never makes it to

If Target.Value = "" Then 'PROBLEM HERE

I cannot help you beyond that. Perhaps someone else can troubleshoot this.