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
 

ghrain22

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

Some videos you may like

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
Joined
Jan 9, 2014
Messages
473
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
Joined
Sep 15, 2014
Messages
7
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
Joined
Jan 9, 2014
Messages
473
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

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