debug.print every mergedarea in a sheet

drom

Active Member
Joined
Mar 20, 2005
Messages
447
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Hi and thanks in advance!


If I wanna check if a sheet containg combined cells I can use:
VBA Code:
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
VBA Code:
    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
VBA Code:
    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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
Hi.
Take a look at this thread. It seems to have what you're after.
loop through merged cells

VBA Code:
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

Active Member
Joined
Mar 20, 2005
Messages
447
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Hi!
I am trying to avoid the use of:
VBA Code:
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:

VBA Code:
    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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I am trying to avoid the use of:
VBA Code:
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

Active Member
Joined
Mar 20, 2005
Messages
447
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
OK I know, but
VBA Code:
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:
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top