Using merged cell data in vba

[keiranwyllie]

Hi all,

Is there a catch to using Target.Cells.Count >1?

I understand there's some issues around vba and merged cells and this piece of code can be used to ignore merged cells.

Is there a way to use this such that

If Target.Cells.Count > 1 Then
    'set the Target value to the actual value of the merged cell
    Target = Target.Address.Value     'I know this doesn't work
End if

I'm aiming to use it in some logging code but the worksheet does make use of merged cells for data entry.

Cheers.

 

[Fluff]

I would advise against using

If Target.Cells.Count > 1 Then

for the simple reason that it will crash if there are too many cells, instead use

If Target.CountLarge > 1 Then

But as you have realised that will not work if you have merged cells. Just another reason why merged cells should be avoided like the plague.

 

[keiranwyllie]

I was able to implement it in a different manner by using .Value = Target.Address to pull the cell reference of the merged cells.