# Conditionally Merging Cells

#### kmh6278

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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### PCL

##### Well-known Member
Let's see how next code works
Code:
``````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

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

#### PCL

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
Code:
``````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
Range(Cells(I, J_BEGIN), Cells(I, J - 1)).MergeCells = 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``````

Replies
2
Views
130
Replies
14
Views
276
Replies
3
Views
129
Replies
2
Views
904
Replies
4
Views
281

### Forum statistics

1,191,213
Messages
5,985,309
Members
439,956
Latest member
FrazzledCat ### 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