We help IT Professionals succeed at work.

Unmerge Cells

Cartillo
Cartillo asked
on
Hi Experts,

I would like to request help create a macro to Unmerge all cells from B4:H98 at Week1 to Week5 sheets and delete all data in this range. However, the format of the cells need to be maintained (wrap and center text) with default border color. Hope Expert could help me create this feature. Attached the workbook for Experts perusal.
Copy-DataNew.xls
Comment
Watch Question

kgerbChief Engineer

Commented:
Hello Cartillo,
I think this will undo the merge and maintain the formatting.

Kyle
Sub UndoMerge()
Dim i As Long
For i = 1 To 5
    With Sheets("Week" & i).Range("B4:H98")
        .MergeCells = False
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
    End With
Next i
End Sub

Open in new window

Author

Commented:
Hi Kyle,

Thanks for the code. Is that possible to delete all the data in this range?
Chief Engineer
Commented:
Here you go.
Kyle
Sub UndoMerge()
Dim i As Long
For i = 1 To 5
    With Sheets("Week" & i).Range("B4:H98")
        .ClearContents
        .MergeCells = False
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Color = RGB(192, 192, 192)
        End With
    End With
Next i
End Sub

Open in new window

Author

Commented:
Thanks a lot Kyle.
kgerbChief Engineer

Commented:
You're welcome:)

Kyle
CERTIFIED EXPERT

Commented:
here is a good start:

 
Sub resetrobstg()
'
' resetrobstg Macro
' reset macro
'

'
    Range("B4:B5").Select
    ActiveWindow.SmallScroll Down:=73
    Range("B4:H98").Select
    Selection.UnMerge
    Selection.ClearContents
End Sub

Open in new window

CERTIFIED EXPERT

Commented:
oops, guess I was too late....  LOL

Explore More ContentExplore courses, solutions, and other research materials related to this topic.