Conditionally Merging Cells

[kmh6278]

I'd like to set up some VBA code that will search for "<H>" and merge the cell containing the "<H>" and the cells to the right of it together. The catch is that the number of cells to the right is going to vary. I'd like this to happen everytime "<H>" is found in the row.

So...

If A1, F1 and H1 contain the "<H>", A1, B1, C1, D1 and E1 would be merged together. Then F1 and G1 would be merged together and the next merge would begin at H1.

Any guidance would be greatly appreciated.

 

[PCL]

Let's see how next code works

Option Explicit
Sub Merge_Cell()
Dim I As Integer
Dim J As Integer
Dim LAST_COL As Integer
Dim BEGIN_Flag As Boolean
Dim J_BEGIN As Integer
    Cells(1, 1).Select
    With ActiveSheet.UsedRange
        For I = 1 To .Rows.Count
            BEGIN_Flag = False
            LAST_COL = Cells(I, Columns.Count).End(xlToLeft).Column
            If (Cells(I, 1) = "") Then
                J_BEGIN = 1
                BEGIN_Flag = True
            End If
            For J = 2 To LAST_COL
                If (Cells(I, J) <> "") Then
                    If (BEGIN_Flag) Then
                        BEGIN_Flag = False
                        Range(Cells(I, J_BEGIN), Cells(I, J)).MergeCells = True
                    End If
                Else
                    If (Not (BEGIN_Flag)) Then J_BEGIN = J
                    BEGIN_Flag = True
                End If
            Next J
        Next I
    End With
End Sub

 

[kmh6278]

Thanks...unfortunately this code deleted the cells between the instances of the "<H>" instead of merging them together. Thoughts?

 

[PCL]

I think I'm doing some confusion.
Could you give an example of the contains of cell A1, B1 etc...
before and after.
Clearly "" DON'T mean empty cell!!

 

[kmh6278]

Sure.

In Cell A1 we find: <H>TEXT FOR A1
In Cell F1 we find: <H>TEXT FOR F1
In Cell H1 we find: <H>TEXT FOR H1

I'd like the contents of A1 to be merged across A1, B1, C1, D1 and E1 and the contents for F1 to be merged across F1 and G1. The contents of H1 would be merged across the next few cells as well, until the program finds the next <H>

I'm using <H> as a marker to tell the program where to start the merge. The merge will end 1 cell before the next <H>.

 

[PCL]

How about this

Option Explicit
Sub Merge_Cell2()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim LAST_COL As Integer
Dim BEGIN_Flag As Boolean
Dim J_BEGIN As Integer
Dim CELL_Value
    Cells(1, 1).Select
    With ActiveSheet.UsedRange
        For I = 1 To .Rows.Count
            LAST_COL = Cells(I, Columns.Count).End(xlToLeft).Column
            J = 1
            J_BEGIN = 1
            BEGIN_Flag = False
            Do
                If (Cells(I, J) = """""") Then
                    If (BEGIN_Flag) Then
'-------        SECOND SEPARATOR  FOUND      -------
                        CELL_Value = Empty
                        For K = J_BEGIN + 1 To J - 1
                            CELL_Value = CELL_Value & Cells(I, K)
                        Next K
                        Application.DisplayAlerts = False
                        Range(Cells(I, J_BEGIN), Cells(I, J - 1)).MergeCells = True
                        Application.DisplayAlerts = True
                        Cells(I, J_BEGIN) = CELL_Value
                        J_BEGIN = J
                        BEGIN_Flag = False
                    Else
'-------        FIRST SEPARATOR  FOUND      -------
                        BEGIN_Flag = True
                        J_BEGIN = J
                    End If
                End If
                J = J + 1
            Loop While (J <= LAST_COL)
        Next I
    End With
End Sub