Merge adjacent cell only to merged cells in a range

1
votes

I am trying to merge cell in the adjacent row into cells that are already merged. I want to merge an adjacent cell in column C, if the adjacent cell in column D is merged. Not all cells are merged in column D. I have code below that provides me with the correct row numbers when I use the variable in a MsgBox, however, when I add the variable to a Range to be merged, every row gets merged. I am thinking it must be some simple, but I just can determine what is causing every row in the range to get merged. I normally don't mess with merging anything, but I need to leave the spreadsheet with these merged cells. Your help to crack this one will be greatly appreciated.

Sub FindMerge()

Dim cell As Range
Dim Lrow As Long

Application.ScreenUpdating = False      'Turn off screen updating. Code runs faster without screen flicker
Application.DisplayAlerts = False       'stops Windows Alerts from poping up

'loops through range to find merged cells
'For Each cell In ActiveSheet.UsedRange    'commented out to try static range below.
For Each cell In Range("D1:D81")

    If cell.MergeCells Then

        If cell.Address = cell.MergeArea.Cells(1, 1).Address Then

'           Msgbox "Row: " & cell.row        'displays correct row number where merged cell is located

           Lrow = cell.row

          Range("C2:O" & Lrow).Merge True     'Unintentionally merges every row

        End If

    End If

Next cell

Application.ScreenUpdating = True       'Turns screen updating back on
Application.DisplayAlerts = True        'Turns Windows Alerts back on

End Sub
1
vbaexcel

1 Answers

0
votes

You're merging all rows starting from row 2 by doing this:

Range("C2:O" & Lrow).Merge

What you most likely want is this:

Range("C1:O1").Offset(Lrow - 1).Merge