Count the number of merged cells

[dsheridan35]

So I am trying to create a macro to count the number of tasks that are completed in a schedule. The schedule is for each day and it runs from 1am to the following midnight. The tasks are inputted as merged cells between the time the task is to take place. So what I need the macro to do is count a range of merged cells as 1 task and not the number of individual cells used to make up the merged cell.

Can anyone help me out?

Thanks

 

[pbornemeier]

Possibly the Range.MergeArea property could assist.

Iterate each rngCell of the desired range
Store rngCell.MergeArea.Address and as a ScriptingDictionary Key and the rngCell.MergeArea.Cells.Count as the associated Item
Count the number of SD Keys that have an item <> 1

 

[Sunny Kow]

Hi give this a try.

Sub CountMergedRange()


Dim Rng As Range
Dim WorkRng As Range
Dim OutRng As Range


On Error Resume Next
Set WorkRng = ActiveSheet.UsedRange
For Each Rng In WorkRng
    If Rng.MergeCells Then
        If OutRng Is Nothing Then
            Set OutRng = Rng
        Else
            Set OutRng = Union(OutRng, Rng)
        End If
    End If
Next
If Not OutRng Is Nothing Then
    OutRng.Select
    MsgBox "There are " & Selection.Areas.Count & " merged range."
End If
End Sub