VBA Express : Excel - Clear Cells in a Range that May Contain Merged Cells

Excel

Clear Cells in a Range that May Contain Merged Cells

Ease of Use

Intermediate

Version tested with

2000, 2002

Submitted by:

Jacob Hilderbrand

Description:

This macro demonstrates how to clear all the cells in a range when there may or may not be merged cells within the range.

Discussion:

In some cases you may need to clear all the cells in a certain range. Now this is easy to do unless there are some merged cells within the range. This macro will first unmerge all the merged cells, then clear the cells, then remerge all the previously merged cells again.

Code:

instructions for use

			Option Explicit 
 
Sub ClearRange() 
     
    Dim DelRange        As Range 
    Dim MergeRange()    As String 
    Dim Cel             As Range 
    Dim i               As Long 
     
     'Change this range as needed.
    Set DelRange = Range("A1:D50") 
     
    For Each Cel In DelRange 
        If Cel.MergeArea.Cells.Count > 1 Then 
            i = i + 1 
            ReDim Preserve MergeRange(1 To i) 
            MergeRange(i) = Cel.MergeArea.Address 
            Cel.MergeArea.UnMerge 
        End If 
    Next 
     
    DelRange.ClearContents 
     
    If i > 0 Then 
        For i = 1 To UBound(MergeRange) 
            Range(MergeRange(i)).Merge 
        Next i 
    End If 
     
End Sub

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code from above in the Code Window.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Tools | Macro | Macros...
Select ClearRange and press Run.

Sample File:

ClearRange.zip 6.71KB

Approved by mdmackillop

This entry has been viewed 123 times.