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]

I don't think that's possible without a macro.

Sub CountMerged()
Dim c As Range
Dim x As Long
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            x = x + 1
        End If
    Next
    MsgBox (x)
End Sub

 

[kho29]

Hi,

Thanks for the code, however what should be the code to put on the excel to give the final answer ? i really dun know how to use the code, so can u help me on that ? really appreciate your help

 

[ghrain22]

Alt+11>Insert>Module
Then copy paste

Then you can run it by hitting the little play button or f5.

I have the output as a message box but you can make it output however/whereever you want.

 

[kho29]

i dun know how to make the output as well, i wan the output to appear on the cell itself .... can u help ?

 

[ghrain22]

What column and row does all of your data start. And where do you want the number to appear? In each merged cell?

 

[kho29]

Say from cell A1:D13 contain all my data, F2 will appear my results, say A1,A2,A3 are merged cells for a job, i wan to count them as 3 days instead of one day. Then within cell A1:D13 also have a single cell say B4 count as 1 day, so total i should count as 4 day of work. this 4 days will be same job and i wan to have other job counts as well ...... hope u can help ...

THank you
kai

 

[ghrain22]

assuming the single cells are also merged:

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
        End If
        Cells(2, 6).Value = x
    Next
End Sub

 

[kho29]

wow ... so by just applying the code the total figure comes out. Say 3 cell merged as 1 so total days are 3 which u did using ur code, can i add other single cell say just 1 cell to make it as total 4 days ? Can it be done? becos my data will have merged and single cells so i need to get the total from them ....
appreciate ur help and patience.

 

[kho29]

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