debug.print every mergedarea in a sheet

[drom]

Hi and thanks in advance!


If I wanna check if a sheet containg combined cells I can use:

Sub Macro1()
Dim rRange As Range
Dim rMergedCells As Range:            Set rMergedCells = ActiveSheet.Cells.Find( _
                                        What:="", After:=Range("A1"), LookIn:=xlFormulas, _
                                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                        MatchCase:=False, SearchFormat:=True)
    If rMergedCells Is Nothing Then Exit Sub
    
    
    MsgBox "YES, we have merged cells eg:" & Chr(10) & Chr(10) & rMergedCells.MergeArea.Address
End Sub

But If I have X different merged Ranges, eg:

• B5:C10
• D9:G15
• X2:X4
• AB2:AC40

How can I get every mergedRange address kind of:


RED

    For Each rRange In ActiveSheet.MergeAreas
      Debug.Print rRange.MergeArea.Address
    Next

If I use the macro recorder in a sheet with merged cells, I can activate them as follows

    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate

But I do not want to activate any range
I would like to get the different mergedarea's address
This is why I would like to know how can I get something like the prior RED wrong macro

 

[Osvaldo Palmeiro]

Hi.
Take a look at this thread. It seems to have what you're after.
loop through merged cells

Sub CheckMerged()

    Dim rngMerged As Range
    Dim rngCell As Range
    
    For Each rngCell In ActiveSheet.UsedRange
        If rngCell.MergeArea.Cells(1, 1).Address = rngCell.Address And rngCell.MergeArea.Cells.Count > 1 Then
            'Do something with the merged cells
            Debug.Print rngCell.MergeArea.Address
        End If
    Next
End Sub

 

[drom]

Hi!
I am trying to avoid the use of:

Application.ScreenUpdating=False 
for each Cell in ActiveSheet.UsedRange

Because the macro takes too long


This is why I am trying to know if maeby there is a way of doing something like:

    For Each rRange In ActiveSheet.MergedAreas
      Debug.Print rRange.MergeArea.Address
    Next

I was using the way you say, but in my case takes too long, So I can't

Thanks anyway

 

[MARK858]

I am trying to avoid the use of:

Application.ScreenUpdating=False 
for each Cell in ActiveSheet.UsedRange

Because the macro takes too long

Puzzled....
Application.screenupdating = False makes no difference with the code @Osvaldo Palmeiro posted as the code doesn't interact with the sheet.

 

[drom]

OK I know, but

Application.ScreeenUpdating=False

is something I always have in my macros
Because 98% of my Macros are interacting with the sheets
The same way I always put in my modules:

Option Explicit
Option Private Module

I am tryng to avoid the use of:
For Each Cell In ActiveSheet.UsedRange 'Because the Usedrange can be hugh, and I have mergedCells in many places

I am trying to know if a collection on MergedAreas exist in VBA (kind of)
Thanks

 

[MARK858]

I am trying to know if a collection on MergedAreas exist in VBA (kind of)

Not as far as I know. How many MergeAreas in there in the sheet? how big is the UsedRange? and most importantly how long is it taking for the code in post number 2 taking to run exactly?