count MERGED cells

[MartinCouture]

Hello all,

I have a row of 30 cells, some of them are merged (2 cells or more, some as high as 30 ie the whole line).

I would like to count the number of cells in that row with a merged cell counting as one.

The real life use of all this is that we use an excel spread sheet as a calendar for the guys and we now have to track the number of days away or on the road. When we go on the raod over 2 days, we merge the 2 cells. When someone goes on the road for a week, we merge that week into one cell.

The original plan was 30-(COUNTA()+COUNTBLANK())=days away.

But I tried COUNTA()+COUNTBLANK() on a row that had 2 cells merged and 5 cells merged, and I get 30 (April) so that tells me that a merge cell count as many cells that it originaly had.

I'm also trying to stay away from macros/VB.

help

thanks

-----------------
[flush]

 

[xlbo]

for the sake of your sanity, I would strongly advise NOT to merge cells

I doubt there is any formula way to determine whether a cell is merged or not.

You are better off adding a 'tag' to a cell to indicate that it is a 'road' day - a simple "r" or "rd" or even a symbol would do it - anything that actually differentiates 1 cell from another

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I strongly agree with xlbo!

Using his tip, consider using Conditional Formatting to highlight groupings of cells that have been 'tagged' with the same code.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[MartinCouture]

Great tip! but...

We are already using the content of the cell to write what the destination is and crew number.

We are using this method to count days off though: sumif(c1:af1,"X") and we use the X for day off. We use conditional formating for that number to go to red if below 8.

Please let me know if you meant something different by "tag". I looked up smart tag and that doesn't work.

my sanity is somewhere on a beach in the caribbean, so it's not lost, yet, just taking a break from me...

-----------------
[flush]

 

[firefytr]

You can only count Merged cells by using SpecialCells and the StatusBar as a visual indicator. And even then the cell(s) must have information in it. Otherwise you'd need a UDF to do so.

But, I agree completely with Geoff and Skip here, do not use merged cells if you can help it. Down the road you'll hate yourself when you have to change the entire layout of your data structure and probably most of your formulas/functions as well.

To get the centering affect of this tool, instead use the horizontal alignment of Center Across Selection.

I think, however, that you may need something else altogether. It sounds as if you would require a different type of data structure here. Can you provide us with detailed information outlining the entire scope of this application along with any constants you may require?

-----------
Regards,
Zack Barresse

 

[firefytr]

I'm sorry, I must apologize, I mis-spoke on my above post, you cannot use SpecialCells to count merged. I use a UDF if I want to select merged cells...

Sub SelectAllMergedCells()
    Dim c As Range, rngMerge As Range, rngTemp As Range
    If ActiveSheet Is Nothing Then Exit Sub
    If Selection.Cells.Count = 1 Then
        Set rngTemp = ActiveSheet.UsedRange
    Else
        Set rngTemp = Selection
    End If
    For Each c In rngTemp
        If c.MergeCells = True Then
            If rngMerge Is Nothing Then
                Set rngMerge = c
            Else
                Set rngMerge = Union(c, rngMerge)
            End If
        End If
    Next c
    If Not rngMerge Is Nothing Then rngMerge.Select
End Sub

-----------
Regards,
Zack Barresse

 

[firefytr]

And here is a UDF to count merged cells ...

Function CountMerged(rngMerged As Range) As Long
    Dim c As Range, rngMerge As Range, rngTemp As Range
    If ActiveSheet Is Nothing Then Exit Function
    If Selection.Cells.Count = 1 Then
        Set rngTemp = ActiveSheet.UsedRange
    Else
        Set rngTemp = Selection
    End If
    For Each c In rngTemp
        If c.MergeCells = True Then
            If rngMerge Is Nothing Then
                Set rngMerge = c
            Else
                Set rngMerge = Union(c, rngMerge)
            End If
        End If
    Next c
    If Not rngMerge Is Nothing Then CountMerged = rngMerge.Areas.Count
End Function

Sub testit()
    MsgBox CountMerged(Selection)
End Sub

-----------
Regards,
Zack Barresse

 

[MartinCouture]

Alright,

I'll try the code

In the mean time, here's some of April's schedule:

http://ops.8wing.ca/downloads/testapr.xls

You also have to assume that a guy with very basic computer skills might be at the scheduling desk, so that's why I'm trying to avoid running macros/vb.

-----------------
[flush]

 

[firefytr]

.. and are you going to give us the password??

-----------
Regards,
Zack Barresse

 

[mintjulep]

Never merge cells. No good can come of it.

 

[xlbo]

Seriously - rather than having to use a long winded UDF to overcome this issue, can you not re-jig the spreadsheet to record data slightly differently ??? If you don't, I guarentee you'll wish you had when it keeps breaking and not working and you have to keep adding bits to it.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

You also have to assume that a guy with very basic computer skills might be at the scheduling desk, so that's why I'm trying to avoid running macros/vb.

That is exactly WHY one ought to consider using code versus allowing free entry. Properly designed code, minimizes errors and guides the user in a desired manner.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[MartinCouture]

firefytr:

sorry about the password, it's removed now.

SkipVought:

Still working on the code...

xlbo:

This spreadsheet became a necessity and we had to come up with something quick. The stats tracking features we are trying to add were initially a "nice to have". Now the stats part of the scheduling are becoming more important. The main goal was to have a visually easy schedule to look at (we have a 50in plasma screen showing this on the wall) and be able to quickly get the information out of it. I'll have a look at not merging cells and come up with an easier solution...maybe a pre-made solution, any schedules out there that would fit our situation ?

-----------------
[flush]

 

[MartinCouture]

I'll be on the road for the next 7 days, I'll attack this again when I get back.

-----------------
[flush]

 

[Gavona]

Do you guys understand the results Martin is after? Not completely sure I do.

However, if you have merged cells the first gets counted in CountA() the subsequent merged ones get counted with Countblank(). I think Martin wants to count all such merged cells.

If x represents a non-merged cell, m represents the first merged cell and 0 the cells that are merged with it then the data and desired results could look like this:
xxxm00xxxm0xxxxxx - Result = 5
xxxm000xxxxxxxxxx - Result = 4
note that the results are different despite there being 3 blank cells in each row.

IF Martin could make it so the ONLY blank cells were merged cells (e.g. in his sample E5 thru' H5 all need to have some content) then there could be a formula solution:
You need to use some helper cells - one for each cell. The first would contain the formula:
=IF(OR(ISBLANK(D5),ISBLANK(C5)),1,0)
This would be copied across the next 30 cells to the right and then all these formulae copied down as many rows as there are records (people) in his data.
A "1" is displayed for every cell that is blank or has a blank cell to the right of it. Sum these for the answer.

Note either the formula in the final column needs tweaking or you need to make sure that the following cell is not blank.

Hope that helps.

Would you users not find it easier to avoid merged cells?


Gavin

 

[firefytr]

In your file, F6 is merged. Why can you not just unmerge it and copy F6 over to I6? You will have your "SIM" value in all cells and you can format (color, border, etc) accordingly.

-----------
Regards,
Zack Barresse