• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Clear Merged Cells VBA

chirayu

Well-Known Member
Hi All,

I wanted to share a tip on how to clear merged cells. There are many ways to do it. However I know of two that I have used.

Generally If you are dealing with a single row that has merged cells e.g. Row 5, then you can use code similar to the one given below e.g.

Code:
Sub SingleRowClear()
    Range("A5").MergedArea.ClearContents
End Sub
However lets say you have many rows with merged cells e.g. 5:15, then you need to define the entire range using a different piece of code e.g

Code:
Sub MultiRowClear()
    Range("A5:E15").ClearContents
End Sub
The problem thus arises that you need to always define the range if you have more than one merged row. So lets say if user adds more merged rows. Then the macro will need to change again to include that range, as we cannot use a large range to cover Merged & Non Merged cells.

...Or at least that's what most websites say. I found a workaround when I was playing with a bit of code. Instead of using ClearContents. We use Value. Normally Value is used to change the cell contents. So why not use it to clear cells out?

With the below code you don't need to define an exact merged range, just the cells that start it e.g. if A5:E5, A6:E6 etc. all the way to A50:E50 is merged. We can just refer to it as A5:A50. Which means that technically you can expand your range to anything and the macro never needs to be updated even if user adds more merged rows. Code is below

Code:
Sub MultiCellValueNuller()
    Range("A5:A50").Value = Null
End Sub
I hope this thread helps those with issues clearing Merged Cells through VBA
 
Last edited:

chirayu

Well-Known Member
@Hui Yup :p I know. But I have a load of templates for the Sales team in my company & they use a lot of merged cells. I guess to make the template look more compact & uniform. Obviously we can change column width but then columns that don't need that much width become too large. So I guess that's why they use merged cells in their templates. That's why I came up with the workaround for them.
 
Top