Current VBA very slow in deleting merged cells

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, I have a VBA which does work, but it is incredible slow. I'm trying to delete rows containing merged cells. The name of the work sheet is "Database". Any suggestions to speed this process along? Thank you,

VBA Code:
Sub DeleteMergedCells()
Dim w As Worksheet
Dim r As Range
Application.DisplayAlerts = False
For Each w In Worksheets
    If w.Name <> "x" And w.Name <> "y" Then
        With w
            For Each r In .UsedRange
                If r.MergeCells Then r.Delete
            Next r
        End With
    End If
Next w
Application.DisplayAlerts = True
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Livin404,
suppose your code looping through big size of ranges.
Try ...
VBA Code:
ActiveSheet.UsedRange.Clear
 
Last edited:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
May be
Try
VBA Code:
Sub DeleteMergedCells()
    Dim w As Worksheet
    Dim r, rr As Range
    Dim rrr As Range
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    For Each w In Worksheets
        If w.Name <> "x" And w.Name <> "y" Then
            With w
                For Each r In .UsedRange
                    If r.MergeCells Then
                        If rr Is Nothing Then
                            Set rr = r
                        Else
                            Set rrr = r
                            Set rr = Union(rrr, rr)
                        End If: End If
                Next
                rr.Delete
            End With
        End If
    Next w
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Or...
VBA Code:
ActiveSheet.UsedRange.ClearFormats
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you just want to unmerge the cells, rather than delete them
VBA Code:
For Each W In Worksheets
    If W.Name <> "x" And W.Name <> "y" Then
        W.UsedRange.UnMerge
    End If
Next W
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you just want to unmerge the cells, rather than delete them
VBA Code:
For Each W In Worksheets
    If W.Name <> "x" And W.Name <> "y" Then
        W.UsedRange.UnMerge
    End If
Next W
Thank you that was my best choice. The VBA I had to follow was one to delete a row that contains a specific word and all the following rows. The problem was the VBA wasn't allowing me to do so on merged cells. With your VBA it works great now thank you. The VBA that follows yours is:

VBA Code:
Sub Delete_Totals()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="*passenger*", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,993
Members
416,891
Latest member
Okomomo

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