Count the merge cells horizontally

[kho29]

hi i would like to count the total cells either merge or not merge horizontally. My worksheet needs to count the number of work days in a month whatever type of job i do. Example cell A1, B1,C1,D1 merged as one cell then total is count as 4 days, then B2 single day so total days is 5 daysAnyone can help, i am a excel idiot..... thank you kai

 

[ghrain22]

Sub CountMerged()
Dim c As Range
Dim x As Long
    x = 0
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            x = x + 1
            Else
                If c.Column = 1 Then
                    x = x + 1
                End If
        End If
        Cells(2, 6).Value = x
    Next
    'ActiveSheet.UsedRange.Select
End Sub

 

[ghrain22]

Also from your code can u explain how do i set the cells of my range of datas ? like if i wan to change my data range to C2:O20

Sub CountMerged()
Dim c As Range
Dim r As Range
Dim x As Long


   Set r = Range("C2:O20")


    x = 0
    For Each c In r
        If c.MergeCells Then
            x = x + 1
            Else
                If c.Column = 1 Then
                    x = x + 1
                End If
        End If
        Cells(2, 6).Value = x
    Next
    'ActiveSheet.UsedRange.Select
End Sub

You can change the range by editing this line:
Set r = Range("C2:O20")

That or you could have it work by a selected(highlighted) range

 

[kho29]

Hi,

after the change the count works but it double the count. like total i got 11 days with merged and single cell when i input the word "work" but the count shows a 22 after i run code.

also is it possible to include count for other different words like if i input "shopping" in another cell will count the "shopping" total.

Hope u can help ? i really got alot to learn from u

thank you very much

 

[ghrain22]

It seems to be working fine for me.

I would highly recommend reorganizing your data so you can utilize functions. This make your spreadsheet more flexible/functional, and wouldn't require a macro. You could probably do this by just repeating items instead of using the merged cells.

 

[Joe4]

Just a note: Merged cells are a huge headache, and should be avoided being used whenever possible. They do not play nicely with thing like sorting and VBA (among other things), and as such, most programmers won't use them.

Here is just one article that discusses some of the issues they present: Not Just Numbers: Excel Tip: Do you really need to merge those cells?