asked on

Merge Cells

Hi Experts,

I would like to request Experts help create a macro to automatically merge the empty cells with the content cell vertically at week1 to week5 sheets. I have manually merged few cells at week 1 (1-4 nov) for Experts to get better view. Hope Experts could help me create this feature. Attached the workbook for Experts perusal.
Copy-DataNew.xls

kgerb

Try this. It will loop through sheets Week1 through Week5 and merge the correct cells. Let me know if it's not what you want.

Kyle

Sub MergeCells()
Dim c As Range, firstaddress As String, rng As Range, i As Long, asht As Worksheet
Set asht = ActiveSheet
For i = 1 To 5
    Sheets("Week" & i).Activate
    Set rng = Sheets("Week" & i).Range("B4:H98")
    With rng
        Set c = .Find("*")
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    If c.End(xlDown).Row <> Rows.Count Then
                        Range(c, c.End(xlDown).Offset(-1)).Merge
                    Else
                        Range(c, Cells(rng.Cells(rng.Cells.Count).Row, c.Column)).Merge
                    End If
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
Next i
asht.Activate
End Sub

Open in new window

gtgloner

Question: Do you want the blank cells immediately below ones with data to be merged with the data cells? I am having trouble seeing a pattern by which some VBA code might be written to do what you want...

Cartillo

ASKER

Hi Kyle,

Thanks a lot for the code. Is that any chance to automatically wrap text set the font size to 6? The actual data usually more longer (sometime up to 60 words). Hope you will consider.

ASKER CERTIFIED SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Hi Kyle,

Thanks a lot for the solution.

Cartillo

ASKER

Hi Kyle,

Hope you will consider this request. The question is to unmerge cells and revert the worksheet into the default format .

https://www.experts-exchange.com/questions/27426005/Unmerge-Cells.html