Merged cells recognition 2

[Luis939]

I can tell if my activecell is a merge of other cells:
If ActiveCell.MergeCells = True Then........

however, is there a way to defintely tell how many cells were used for that merge, and also which cells, thanks!!

 

[SkipVought]

Hi

Make a little test procedure with the above code.

Step into the procedure and use the watch window to explore the merged range.

Lots of stuff you can learn that way!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

Well im not really able to do much with that code thats all i had, but the merged cell is now just one cell so i guess thats the only way i can treat it

 

[VBAjedi]

Luis939,

Actually, there is a way. Your golden ticket is the MergeArea property. Something like the following should get you what you want:

Sub IsMerged()
Dim c As Range
If ActiveCell.MergeCells = True Then
   For Each c In ActiveCell.MergeArea
      MsgBox c.Address
   Next c
End If
End Sub

Let me know if that's what you're after!

VBAjedi

 

[SkipVought]

Luis939,

1. merge cells A1:B2

2. enter code

[a1]=1

3. step into code

4. select [a1] for watch window

5. observe the wonders as you open up A1
Cells
MergeArea

etc

Look at [a1].mergearea.address

I find ALOT about properties using this approch.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

Thanks, I wasnt aware ofthe mergearea property, but i will defintely play around with the code.

 

[VBAjedi]

Skip,

I've never used the Watch window as a properties explorer before - that is SICK! Have a star (wish I could give you two!).



VBAjedi

 

[Luis939]

I'm not too familiar with the Watch window, how exactly would I use it to my advantage.

 

[Luis939]

In other words, what is the watch window? thanks

 

[SkipVought]

You either step into your macro OR set a break at an appropriate place in your code.

Then (or even before you start running your code) you ADD Watch... by right clicking an object or variable and observing the value(s) displayed in the Watch Window.

You can even Add Watch objects that are not even in your code, as long as they are valid objects (ranges, controls, charts, pivottables) within the context of the procedure.

For instance, if you have a merged cell area A1:C3.
Add Watch [B2].
Step into your code. -- observe that the Expression display changes IMMEDIATELY and you can OPEN UP [B2]
When you do, scroll down to MergeCells -- notice that the value is True.
Also notice just above that the MergeArea can be opened. This is all the INFO that Excel has about the Merged Area A1:C3.
If you did an Add Watch on [B2].mergearea.address, you'll see A1:C3 -- Holy mackerel! an answer to one of your questions!
[B2].mergearea.cells.count gives you another answer.

The REAL answer is that NOW you know what object properties to use to get the answers in your code when you run it.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com