Excel VBA: Merging cells in several columns based on the cell values of one column with some exceptions

[SamanthaSchmith]

Hello,

I'm using the following code from a previous post:

Excel VBA: Merging cells in several columns based on the cell values of one column

Option Explicit
Sub MergeCellsBasedOnColumnRValues()

Dim lX As Long, lMergeStart As Long
Dim vMergeStartValue As Variant

lMergeStart = 1
vMergeStartValue =Cells(1, "A").Value

Application.ScreenUpdating = False

For lX = 2 To 1000
IfCells(lX, "R").Value <> vMergeStartValue Then
If lX <> lMergeStart Then
MergeAToTColumnsInSpecifiedRows lMergeStart, lX - 1
lMergeStart = lX
vMergeStartValue =Cells(lX, "R").Value
End If
Else
'this row will be merged with previous when the block if finished, keep going to check next row
End If
Next

Application.ScreenUpdating = True

End Sub
Sub MergeAToTColumnsInSpecifiedRows(lMergeRowStart, lMergeRowEnd)

What this code does is: merge cells with the same value in column A and merge the following columns in the same rage as A. i.e. if A2:A23 is merged then C2:C23, D2:D23, E2:E23 etc. will also be merged.

But I need to add some modifications for this code to work properly with my case. I need to add some exception in the column merge. i.e. I want to merge in the same range as A columns B:F, then from J:K and N:P.
The columns G, H, L and M should keep the same.

Additionally, I would like to add an exception and if the value of column A is "Spare" do not merge any cell.

In advance thanks for your help.

 

[JackDanIce]

Welcome to the board. Merged cells and VBA does not combine very well at all, usually it's avoided.

Best advice: redesign your spreadsheet and do not use merged cells, use cell shading or other formatting methods over merging cells.