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.
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
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
I hope this thread helps those with issues clearing Merged Cells through VBA
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: