Clear Merged Cells VBA

[chirayu]

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.

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

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

Sub MultiCellValueNuller()
    Range("A5:A50").Value = Null
End Sub

I hope this thread helps those with issues clearing Merged Cells through VBA

 

[Hui]

This will unmerge all cells in the current worksheet

Sub UnMergeAll()
  ActiveSheet.Cells.UnMerge
End Sub

 

[chirayu]

@Hui Yup 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.

 

[Attarwala Shehzad]

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.

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

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

Sub MultiCellValueNuller()
    Range("A5:A50").Value = Null
End Sub

I hope this thread helps those with issues clearing Merged Cells through VBA

Thank You
It worked