Clear Contents Macro Help

What is the error?

Hi Jonmo,

I was receiving the error (which I didn't actually read until you asked-whoops): Run-time error '1004': We can't do that to a merged cell.

The original macro I was trying to modify is:

Sub quarter1()
Range("G21").Select


Do While Selection.Row < 200
Selection.ClearContents
Selection.Offset(3).Select
Loop
Application.Goto (ActiveSheet.Range("A19"))
End Sub

I'm moving the buttons the macros were tied to to the Update sheet, rather than keep them on the Action Plan sheet.

The data I need to be erased are in merged cells, but the macro had no problem erasing them when the buttons were on the Action Plan sheet.

There are four columns I want to be erased (each with their own button/macro).

Columns G, K, O, S. Rather than starting the erasure on g21 (as is in the original macro- I think I changed the row layout since), I need to erase G18:G21, then G23:G26, etc. until G163:G166. The ranges will be the same, just the column letter will differ between the macros.

I want these to run from the Update sheet without jumping to Action Plan (without making it the activesheet in other words).

In each of those 4 cell ranges, there are two merged cells (each spanning two rows, three columns. I don't think the extra columns play into it since its only the first upper left cell that determines content for merged cells). Since there is a spacer row between the part where people would enter data, I guess that might have been causing my error.

Any thoughts?

Well, the error is pretty self explanatory (sorry if that sounds sarcastic, it's not meant to be).

You have to make sure the entire merged range is contained within the range referenced to be cleared.
You can't have half of a named range in the clearcontents command, like
Range("A1:A3").ClearContents if A3:A4 are merged.

Also consider the columns, if it's a multi column merged range..

So for range, would I do something like G18:I21? (That would cover both data cells in that range).

How do I then loop it so I don't have to type out every single range? There is a one row space between all the ranges.

For these ranges specifically
G18:G21, then G23:G26, etc. until G163:G166

you could do

Forgive my ignorance (I'm only just starting to learn more about macro code), how would I combine these statements into a working macro then? I tested the macro just based on the G18:i21 range and it did erase what I wanted, just need to account for the other 29 ranges. If you're new code will, how do I combine them?

Truly appreciate the help!

Sub quarter1()
Sheets("Action Plan").Range("G18:i21").ClearContents
End Sub


For i = 18 to 163 Step 5 Cells(i,"G").Resize(4,1).ClearContentsNext i</pre>

We just need to add the sheet reference..and expand to cover columns G to I

I ended up getting the same error I originally received : /.

The only thing I really changed was to expand the columns.

Do your named ranges cover multiple columns G to I ? Or is it just G ?

Change the 3 back to a 1