CODEWITHSUNDEEP
vbaexcelmergerange
Berker Yüceer
Berker Yüceer

Reputation: 7325

How to find if range contains merged cells in VBA

I have an Excel document that contains duty shifts. I would like to findout if there is any merged cells like given below withing given range..

Example

How can I determine the cells are filled in given range or the cells are merged in given range?

If IsEmpty(Range("NewRange")) = False Then
    z = z + 1 'My counter 
End If

I tried IsEmpty Function but it doesnt work correctly on merged cells. You can try but the result is same.. While I got a block of empty cells there it counts as filled..

Upvotes: 2

Views: 8180

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

MS Excel 2010 and higher has Range.MergeCells Property (Excel), which returns true if if the range contains merged cells.

Another method you'll find here: http://www.exceltrick.com/how_to/find-merged-cells-in-excel/

Dim r As Range
Set r = Range("A9:G10")
With r
    If IsNull(.MergeCells) = True Or .MergeCells = True Then
        z = z + 1
    End If
End With

Upvotes: 6

Related Questions