Merging Cells

[Ginger-daq]

Hi,

How would I go about merging cells with the same values AUTOMATICALLY?

For instance in cells A1:E1, each cell contains a value of 4 except E1 so I require A1:D1 merged.

Driving me insane!! limited excel and VBA knowledge sorry.


Ginger-daq

 

[Ginger-daq]

I either have one of the following formula's depending on what I want shown:

1) =IF(AND($B4<=F$3,$C4>=F$3),$A4,"") This gives me a text dipslay of the task details.

2) =IF(AND($B4<=F$3,$C4>=F$3),(1*$D4),"") This shows the number of staff allocated to the task.

Both merge fine, it's just the cells that are the sum of the formula equal to "" that I do not want merged.

Hope this makes sense?

Ginger-daq

 

[iliace]

If rngBegin Is Nothing [COLOR=red][B]Or VBA.Len(Rng.Value) = 0 [/B][/COLOR]Then

 

[Ginger-daq]

Nothing happens now???

 

[iliace]

That's because I'm an idiot. This should work correctly, post back if not. Take the full procedure code and replace the For loop with the following:

  For Each rng In rngAll.Cells
    If rngBegin Is Nothing Then
      Set rngBegin = rng
    ElseIf rng.Value <> rngPrev.Value Or rng.Row <> rngPrev.Row Then
      If (VBA.Len(rngPrev.Value) > 0) Then
        Set rngEnd = rngPrev
        Set rngMerge = Range(rngBegin, rngEnd)
        If rngMerge.Cells.Count > 1 Then
          Application.DisplayAlerts = False
          rngMerge.Merge
          Application.DisplayAlerts = True
        End If
      End If
      Set rngBegin = rng
    End If
    Set rngPrev = rng
  Next rng

 

[Ginger-daq]

Idiot you certainly are not!... Works an absolute treat!!!!!!

One final request if you're up for it??...


Once this has been produced, can I sort the tasks from individual lines into a 'Best fit' scenario? i.e. multiple tasks per line.

Probably not very clear. I'll try and download that visual thingy to disply what I mean after this post.


Ginger-daq

 

[wotlarx]

Greetings!

This post was incredibly helpful - the code you posted, iliace, works a treat!

I'm a complete novice and I almost fell off my seat when my cells came together so beautifully.

Like Ginger-Daq, I am also merging cells that contain text and numbers - but I have one more question for you: is it possible to also merge the EMPTY cells within the range?

Thanks in advance!

wotlarx

 

[iliace]

Greetings!

This post was incredibly helpful - the code you posted, iliace, works a treat!

I'm a complete novice and I almost fell off my seat when my cells came together so beautifully.

Like Ginger-Daq, I am also merging cells that contain text and numbers - but I have one more question for you: is it possible to also merge the EMPTY cells within the range?

Thanks in advance!

wotlarx

I believe it would, if you take out this conditional:

If (VBA.Len(rngPrev.Value) > 0) Then

and the corresponding

End If

 

[ciprianmp]

...

Thank you iliace, great code!
Before I try it out, I wonder if the unmerge is also done automatically when the code runs (to unmerge previously merged cells/rows within the range). Do we need an extra "unmerge" subroutine ahead?
Can this macro code be run automatically if the cell content changes?

What I try to do, combined with conditional formating, is:

1. The main range I want to run "merge" take values from a shadow identical range (outside the visible page). That gives cells a specific format with conditional formating. I want to keep the main range readonly, so the editors only change data in the shadow range (actually the source is linked to an editable sheet, separate).
2. Then, in the main range I want to merge the adiacent cells only (columns and rows) that have identical values (Not Null) - mostly text, but your code is already doing that for numbers so no problem.
3. When the editable range has cells with updated values (either emptied or text changed), I want the Main Range to be unmerged, then consecutively remerged based on the new content.
Of course, the colors and other formats will also update, automatically based on the new values in the shadew range.

It would be great if it would work automatically on cell update, but also a manual button could work (if there is a limitation in the first case).

The final code you provided was this:

[LIST=1]
[*]Public Sub mergeSameCell()
[*]  Dim rng As Excel.Range, rngPrev As Excel.Range
[*]  Dim rngAll As Excel.Range, rngMerge As Excel.Range
[*]  Dim rngBegin As Excel.Range, rngEnd As Excel.Range
[*]  If VBA.TypeName(Selection) <> "Range" Then Exit Sub
[*]  Set rngAll = Application.Selection
[*]  Application.Calculation = Excel.xlCalculationManual
[*]  Application.ScreenUpdating = False
[*]  For Each rng In rngAll.Cells
[*]    If rngBegin Is Nothing Then
[*]      Set rngBegin = rng
[*]    ElseIf rng.Value <> rngPrev.Value Or rng.Row <> rngPrev.Row Then
[*]      If (VBA.Len(rngPrev.Value) > 0) Then
[*]        Set rngEnd = rngPrev
[*]        Set rngMerge = Range(rngBegin, rngEnd)
[*]        If rngMerge.Cells.Count > 1 Then
[*]          Application.DisplayAlerts = False
[*]          rngMerge.Merge
[*]          Application.DisplayAlerts = True
[*]       End If
[*]      End If
[*]      Set rngBegin = rng
[*]    End If
[*]    Set rngPrev = rng
[*]  Next rng
[*]  Set rngEnd = rngAll.Cells(rngAll.Cells.Count)
[*]  Set rngMerge = Range(rngBegin, rngEnd)
[*]  If rngMerge.Cells.Count > 1 Then
[*]    Application.DisplayAlerts = False
[*]    rngMerge.Merge
[*]    Application.DisplayAlerts = True
[*]  End If
[*]  Application.Calculation = Excel.xlCalculationAutomatic
[*]  Application.ScreenUpdating = True
[*]End Sub
[/LIST]

Can you adjust it for my needs please? I really apreciate it!

 

[iliace]

To un-merge merged cells, select the range in question and run the following code:

Public Sub unmergeSameCell()
  Dim rng As Excel.Range, rng2 As Excel.Range
  Dim rngAll As Excel.Range
  Dim rngMerged As Excel.Range
  
  If VBA.TypeName(Selection) <> "Range" Then Exit Sub
  
  Set rngAll = Application.Selection
  Application.Calculation = Excel.xlCalculationManual
  Application.ScreenUpdating = False

  For Each rng In rngAll.Cells
    If rng.MergeCells Then
      Set rngMerged = rng.MergeArea
      rng.UnMerge
      For Each rng2 In rngMerged.Cells
        rng2.Formula = rngMerged.Cells(1).Formula
      Next rng2
    End If
  Next rng
  
  Application.Calculation = Excel.xlCalculationAutomatic
  Application.ScreenUpdating = True


End Sub

You can call this procedure at the top of the original code (before the For Each loop) to un-merge previously merged cells before re-merging.

It's possible to automate it based on Worksheet_Change() event or something similar.

To explore the feasibility of the additional features you're looking for, I would need a more detailed description of the "main range" and "shadow range" and how they're linked. If you can type up a dummy example showing both, before and after, and including formulas and formats (and how they're linked) that would be most helpful.

Also, I'm not sure how you would want to handle multiple rows. Let's say your data looks like this:

4 4 4 5 5
4 4 5 5 5

Do you merge A1:B2? or A1:C1 and A2:B2 separately (current behavior)?

Do you merge D1:E2? or D1:E1 and C2:E2 separately (current behavior)?

 

[ciprianmp]

Also, I'm not sure how you would want to handle multiple rows. Let's say your data looks like this:

4 4 4 5 5
4 4 5 5 5

Do you merge A1:B2? or A1:C1 and A2:B2 separately (current behavior)?

Do you merge D1:E2? or D1:E1 and C2:E2 separately (current behavior)?

Thank you for your fast reply. I will answer this first, as for samples it will take longer (my workbook is on a non-internet connected pc).

So no, adiacent cells should always form rectangulars in the form, like this:
4 4 4 5 5 7 7 ""
4 4 4 5 5 6 6 6
"" "" ""... 6 6 6

So different number of columns and rows for each possible value, but always same lenght and height. It is actually a 3 column hourly schedule (hours splitted by each row on 15' interval, 3 teams one for each of the collumns).

Actually, a nice warning if the user makes it wrong and a highlight of the wrong input cells would be nice to implement before merging.

BRB with the format/shadow explanation, although it doesn't impact the merge/un-merge subroutine.