EXCEL 2010 - Clear Contents Button for merged cells

Question

EXCEL 2010 - Clear Contents Button for merged cells

Anonymous

Hi, I am trying to create a button that clears the contents of various cells in a tab. The issue is that many are merged cells. Here is what I had come up with:

Sub Clearcells2()

'Updateby DVMay2018

Range("E8").ClearContents

Range("E12").ClearContents

Range("E16").ClearContents

Range("E20").ClearContents

Range("E22").ClearContents

Range("E24").ClearContents

Range("E26").ClearContents

Range("E28").ClearContents

Range("E30").ClearContents

Range("E32").ClearContents

Range("E34").ClearContents

Range("D42").ClearContents

Range("D44").ClearContents

Range("D46").ClearContents

Range("D48").ClearContents

Range("D50").ClearContents

Range("D55").ClearContents

Range("D57").ClearContents

Range("D59").ClearContents

Range("D61").ClearContents

Range("D63").ClearContents

Range("D65").ClearContents

Range("D67").ClearContents

Range("D69").ClearContents

Range("D71").ClearContents

Range("D75").ClearContents

MsgBox "Form has been CLEARED"

End Sub

Obviously wrong, but how would I do it if E8, 12, 16 are merged?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

@DV123

Instead of hard-coding addresses (they might change…) in your Sub I would suggest you:

Select E8, E12, E16, … D75
Name that selection ie. myRange

Then your Sub should be:

Sub ClearForm()

'

Dim targetRange As Range

Dim c As Range

Set targetRange = Range("myRange")

For Each c In targetRange.Cells

If c.MergeCells Then

c.MergeArea.ClearContents

Else

c.ClearContents

End If

Next c

End Sub

Share via

EXCEL 2010 - Clear Contents Button for merged cells

0 additional answers