[Solved] How to clear merged cells

[Bryan]

I am hoping to get a macro that does the following. I need to clear data from merged cells on 3 different sheets in the same workbook .
The range for sheet 1 and sheet 2 is B31 down to B47. Sheet 3 is from B37 to B47.
I have tried every formula I could find to achieve this, but couldn't get any to work due to the problems around clearing merged cells.

 

[Lutfor Rahman Shimanto]

I am hoping to get a macro that does the following. I need to clear data from merged cells on 3 different sheets in the same workbook .
The range for sheet 1 and sheet 2 is B31 down to B47. Sheet 3 is from B37 to B47.
I have tried every formula I could find to achieve this, but couldn't get any to work due to the problems around clearing merged cells.

Hello Bryan

Welcome to ExcelDemy Forum! Thanks for reaching out and posting your query.

You want to clear data from merged cells on 3 different sheets in the same workbook. The range for sheets 1 and 2 is B31 down to B47. Sheet 3 is from B37 to B47.

I am delighted to inform you that I have developed a VBA Sub-procedure to reach your goal.

Initial Sheets with Merged Cells:

​

Excel VBA Code:

Sub ClearMergedCellsData()

    Dim ws As Worksheet
    Dim rng As Range
    
    For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                Set rng = ws.Range("B31:B47")
            Case "Sheet3"
                Set rng = ws.Range("B37:B47")
            Case Else
        End Select
        
        For Each cell In rng
            If cell.MergeCells Then
                cell.MergeArea.ClearContents
            End If
        Next cell
    Next ws

End Sub

OUTPUT:

OUTPUT VIDEO​

I am also attaching the solution workbook for better understanding. Stay Blessed.

Regards
Lutfor Rahman Shimanto

 

[Bryan]

Hi, sorry, looks like I didn't explain my problem properly.
The merged cells go from B31 to P31, then B31 to P32 and so on till the last line at B47 to P47 for sheets 1 and 2
Sheet 3 is B31 to P31 down to last line from B47 to P47
Im guessing that is what is causing the run-time error 9, subscript out of range error message when the line
For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
is run?

 

[Lutfor Rahman Shimanto]

Hi, sorry, looks like I didn't explain my problem properly.
The merged cells go from B31 to P31, then B31 to P32 and so on till the last line at B47 to P47 for sheets 1 and 2
Sheet 3 is B31 to P31 down to last line from B47 to P47

Hello Bryan

Thank you for posting your query with such clarity. After analyzing the new requirement, I understand that you want to clear contents from the merged cells within the B31:P47 range for sheets 1 and 2. For sheet 3, the code will remove contents from the merged cell within the range B31:P31 .

I have modified the previous code to achieve your goal. I had only to define the intended ranges you mentioned. The rest of my previously given codes are the same.

Excel VBA Code:

Sub ClearMergedCellsData()

    Dim ws As Worksheet
    Dim rng As Range
 
    For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                Set rng = ws.Range("B31:P47")
            Case "Sheet3"
                Set rng = ws.Range("B31:P31")
            Case Else
        End Select
     
        For Each cell In rng
            If cell.MergeCells Then
                cell.MergeArea.ClearContents
            End If
        Next cell
    Next ws

End Sub

INITIAL Sheets:

​

OUTPUT Sheets:

​

I am attaching the solution workbook for better understanding. Good luck.

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Im guessing that is what is causing the run-time error 9, subscript out of range error message when the line
For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
is run?

Dear Bryan

The error you were having was because one of the sheets among sheets 1, 2, and 3 was missing.

​

Things to Remember: Ensure that all the sheets 1, 2, and 3 exist in your workbook.

Regards
Lutfor Rahman Shimanto