CODEWITHSUNDEEP
excel
Naz
Naz

Reputation: 910

VBA merged cells need to be the same size

I have the following code that sort data, a task list, in a spreadsheet, first it sorts by task priority pushing 'Completed' to the bottom, then it sorts by date excluding the tasks completed tasks;

Sub mcr_PushDown_Completed_Taks()
'
' Push down completed tasks

'

    'Turn off screen updating so macro working/flashing does not show
    Application.ScreenUpdating = False

    With Worksheets("Tasks")

        With .Range(.Cells(7, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 10))

            .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes

            With .Resize(Application.Match(7, .Columns(1), 0) - 1, .Columns.Count)

                .Cells.Sort Key1:=.Columns(2), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlYes

            End With

        End With

    End With

    Range("B7").Select

End Sub

However, all of a sudden when I run the macro I get the error

To do this, all the merged cells need to be the same size.

However, I can't find any merged cells on the sheet using the Find dialogue box. How else can I find merged cells or resolve this?

Upvotes: 0

Views: 4141

Answers (2)

Naz
Naz

Reputation: 910

Someone renamed the sheets hence the code was working on a different sheet.

Upvotes: 0

rohrl77
rohrl77

Reputation: 3337

This sub will find merged cells. It uses the .MergeArea property of a range in finding a cell that is merged. VBA doesn't offer a cell property that would return a simple "TRUE" to the question "am I part of a merged area", so you need to evaluate for your self if the area of the cell is the same as a regular cell or if it is part of a larger area.

Sub FindMergedCells()
Dim Rng As Range
Dim c As Range
Set Rng = Selection

With Rng
    For Each c In Rng
        If Not (c.MergeArea.Address = c.Address) Then
            Debug.Print c.Address & " is a merged cell"
        End If
    Next c
End With

End Sub

Upvotes: 2

Related Questions