Count the merge cells horizontally

kho29

New Member
Joined
Sep 15, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
I don't think that's possible without a macro.

Code:
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

New Member
Joined
Sep 15, 2014
Messages
7
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

Active Member
Joined
Jan 9, 2014
Messages
473
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

New Member
Joined
Sep 15, 2014
Messages
7

ADVERTISEMENT

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

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
What column and row does all of your data start. And where do you want the number to appear? In each merged cell?
 

kho29

New Member
Joined
Sep 15, 2014
Messages
7

ADVERTISEMENT

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

Active Member
Joined
Jan 9, 2014
Messages
473
assuming the single cells are also merged:

Code:
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

New Member
Joined
Sep 15, 2014
Messages
7
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

New Member
Joined
Sep 15, 2014
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,204
Messages
5,629,277
Members
416,383
Latest member
Joe Reiter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top