0

Let say I have (A1, B1), (A2, B2), (A3, B3) merged. How can I clear the contents of those cells proving only first column and rows, .Range("A1:A3")?

I don't want to use loop because I have many merged range to work with and looping each cell in range takes too long. The worksheets are given by another party. I am not allowed to break the merged cells.

mrr010
  • 13
  • 1
  • 6
  • Have you tried (assuming sheet1 - change as needed) `Sheet1.Range("A1:B3").ClearContents` –  Mar 22 '21 at 08:51
  • I found the problem. I actually need to clear contents providing only the first column and rows "A1:A3". I don't want to check the ending column of each merged area. Is it possible to do that? – mrr010 Mar 22 '21 at 09:07
  • @mrr010 See my answer below. This works as shown in the animation. – Pᴇʜ Mar 22 '21 at 09:08
  • `Range("A1:A3").Value = vbnullstring` will work if you only know the first column address. – Rory Mar 22 '21 at 10:47

2 Answers2

1

If you have those cells merged (A1, B1), (A2, B2), (A3, B3), the following code will clear it:

ActiveSheet.Range("A1:B3").ClearContents

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • What if I only know the first column is A but I dont know how many columns or rows each merged cell has? – mrr010 Mar 22 '21 at 09:11
  • @mrr010 If you don't know how many columns/rows are merged you need to loop through the first column and find out the `MergeArea` of each first cell to then clear it. This is not possible without looping then. `MergeArea` only works with one cell but not with multiple cells: `Range("A1").MergeArea.ClearContents` works but `Range("A1:B3").MergeArea.ClearContents` does not. So you need to loop `A1`, `A2`, … – Pᴇʜ Mar 22 '21 at 09:15
0

Recording a macro is about 90% of the code:

Sub Macro1()
    Range("J4:M15,E5:G14,E5:G14").Select
    Range("E5").Activate
    Selection.ClearContents
    Range("G18").Select
End Sub

Then avoiding Select and Activate results in this:

Sub Macro1()
    Worksheets(1).Range("J4:M15,E5:G14,E5:G14").ClearContents
End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100