Merge Cells based on used cells in column 5

Re: Merge Cells based on used cells in column 5

No idea what you mean...

Please upload a sample workbook showing before and after.

On another tack, why would you want to merge cells? It makes it nearly impossible to manipulate the data.

Re: Merge Cells based on used cells in column 5

Could not understand your actual scenario. But still, you can use the below code for Merging cells.

Sub Merge_cells()
    ThisWorkbook.Sheets(1).Activate
    ThisWorkbook.Sheets(1).Range("B4:D4").Select
    Selection.Merge
End Sub

Re: Merge Cells based on used cells in column 5

There's no attachment.

However, I would not ignore Cytop's advice on merging cells, quite simply - don't!

Re: Merge Cells based on used cells in column 5

Try

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim iCount As Integer
    
    If Not Intersect(Range("C7:I7"), Target) Is Nothing Then
        
        iCount = Application.WorksheetFunction.CountA(Range("C7:I7"))
        Range("C4:I7").HorizontalAlignment = xlGeneral
        Range("C4").Resize(, iCount).HorizontalAlignment = xlCenterAcrossSelection
    End If
    
End Sub

But note that if the selection is too narrow (2 or 3 cells only) then the text will offset to the left so it centers over the existing text... This can be avoided by other formatting like row height and word wrap.