selection.MergeCells property changes the original selection in excel

Question

I want to check if my selection contains MergeCells and if so I do not want to process that selection in excel. But whenever I call selection.MergeCells the original selection changes and it wraps the areas bounded by MergeCells which I DO NOT want. I have checked the below MSDN link, which tell that it is a limitation/bug and can not be solved , so can anyone please help me?

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/63920347-c731-4046-b96f-3f3e7eabddd8

when I use below line of code to check if MergeCells present in the current Range/Selection may be partially or fully then the original selection expands automatically. if (Convert.IsDBNull(rg.MergeCells) || (bool)rg.MergeCells == true) — Sagar, May 09 '13 at 07:54
I think that we need a procedure that can duplicate what you are seeing, so that we can reliably test possible solutions. Something like the steps described at the link you posted plus some simple code you want to execute that cause the problem. Then we can concentrate on getting the code to work without causing the problem. — RBarryYoung, May 09 '13 at 12:55
@RBarryYoung if you want the reproduction steps: in a new spreadsheet, merge cells A1 and B1. Manually select column A. In your macro editor, create a public Sub containing only the line `Debug.Print Selection.mergeCells` and run that macro. The user selection has now been extended to `A:B`. And this is true even if you use `Debug.print Range("C:C").mergeCells` in your macro. — Faibbus, Aug 28 '20 at 11:08

score 0 · Answer 1 · answered Oct 18 '18 at 16:25

I ran across this post and discovered that the answer does not work.

To see why, try this code

Cells.UnMerge
Cells.ClearFormats

[4:4,11:11,21:21].Select

Selection.Interior.Color = vbYellow

[a2:b5].Merge
[a19:b22].Merge

Debug.Print Selection.Address
Debug.Print [a1].MergeCells
Debug.Print Selection.Address
End Sub

The only solution is something like the following. Sorry I don't have time to flesh it out anymore.

dim mergedCells as collection, oldSelAddress as string, var as variant

oldSelAddress = selection.address
debug.print [a1].mergecells
if oldSeladdress <> selection.address then 
   set mergedCells = collectMergedCells(selection)
   selection.unmerge
   range(oldseladdress).select
   for each var in mergedCells
          var.merge
   next
end if

score -1 · Answer 2 · answered May 08 '13 at 14:48

The problem in your case is slightly different from the link that you point to (because they are in the SelectionChanged event). In your case, the problem is that you are using Selection.MergeCells in you VBA code. Don't do that.

You should always avoid using Select-related actions in your own VBA code (because it is slow and worse, has tons of unintended side-effects, like this). Instead, use range objects. But because the Selection Range itself is so closely bound to the Selection object, you may need to disassociate it, like so:

Dim rng As Range, ws As Worksheet
'get the current worksheet
Set ws = ActiveSheet
'get the selection-range
Set rng = Selection
'get the same range, but disassociated from the selection
'(I think this works?)
Set rng = ws.Range(rng.AddressLocal)

If rng.MergeCells Then ...

Let me know if this does not work (I cannot test it right now), as there is a more complicated approach that can be used intead.

I have tried this but no luck , I tried using C# and also using VBA MACRO but in both cases it expands the selection to include MergeArea , so what to do now ? — Sagar, May 09 '13 at 07:52

selection.MergeCells property changes the original selection in excel

2 Answers2