# Count the merge cells horizontally

#### kho29

##### New Member
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

##### Active Member
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
Else
If c.Column = 1 Then
x = x + 1
End If
End If
Cells(2, 6).Value = x
Next
'ActiveSheet.UsedRange.Select
End Sub``````

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### ghrain22

##### Active Member
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

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

##### New Member
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

##### Active Member
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?

Replies
8
Views
141
Replies
2
Views
75
Replies
4
Views
168
Replies
9
Views
219
Replies
0
Views
32

1,127,612
Messages
5,625,839
Members
416,138
Latest member
Pizzaman22

### 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.

### Which adblocker are you using?

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

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