Clear Contents Macro Help

[liquidmettle]

Hi All,

I'm fairly sure this will be an easy one, just can't figure out where I've gone wrong.

I have a sheet named "Update" that I want to put a button on to erase a range (just the contents) on a sheet called "Action Plan".

I do not want to make Action Plan the activesheet, I just want the button to erase it from the Update sheet.

I am trying to use this macro code, but its telling me there is an error:

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

Something I'm doing wrong?

Thanks in advance!

-LM

 

[liquidmettle]

I actually didn't make these areas named ranges. I could make a non-contiguous named ranged if you think it would make it work easier.

G18:i19 covers the first merged cell. G:20:i21 covers the second (no rows between these). So g18:I21 covers the first total range I want erased (sorry if I'm repeating anything, I just want to be as clear as possible). The second "range" would be G23:i26, etc. to g163:i166. There is a one row space between the range g18:i21 and g23:i26, adnasuem through the ranges.

I can control highlight all these areas and make them a named range if necessary, I just want to make sure thats what the macro requires to function before I do it.

 

[Jonmo1]

I actually didn't make these areas named ranges. I could make a non-contiguous named ranged if you think it would make it work easier.

Sorry, I meant to say 'Merged', not 'Named' LOL..


Are you sure of the 'actual' merged ranges?

What does this say
MsgBox Sheets("Action Plan").Range("G18").MergeArea.Address

and
MsgBox Sheets("Action Plan").Range("G20").MergeArea.Address

 

[liquidmettle]

I replaced the line starting with "sheets" in the macro code with your "msgboxsheets" line for g18 and ran the macro. The box that popped up said g18:i19 (with the dollar signs that i didn't retype).

And yes, about the ranges, I have the sheet open before me and the ranges are what I mentioned above.

I'm quickly learning "simple" isn't always what I thought : p.

 

[Jonmo1]

Perhaps I should have started with this...

Merged cells and VBA go together about as well as Oil and Water.
They really should be avoided at all costs.
They 'cause' more problems than they 'solve'

Anyway, we should still be able to get through this.


Perhaps there's 1 stray incorrect merged range throwing it off, not necessarily that all of them are off...
What is the value of i at the time you get the error?
You can just hover your mouse over the i after you click Debug.

 

[liquidmettle]

It says i=18

 

[Jonmo1]

what was the result of

MsgBox Sheets("Action Plan").Range("G20").MergeArea.Address

 

[liquidmettle]

$G$20:$i$21

 

[Rick Rothstein]

Perhaps I should have started with this...

Merged cells and VBA go together about as well as Oil and Water.
They really should be avoided at all costs.
They 'cause' more problems than they 'solve'

AMEN!!!!

Anyway, we should still be able to get through this.

Give this code a try...

Sub ClearCellsSomeOfWhichAreMerged()
  Dim Index As Long, Cell As Range
  Index = 18
  Do While Index <= 166
    Set Cell = Cells(Index, "G")
    If Cell.MergeCells Then
      Cell.MergeArea.ClearContents
      Index = Index + Cell.Rows.Count
    Else
      Cell.ClearContents
      Index = Index + 1
    End If
  Loop
End Sub

 

[Jonmo1]

And the error specifically says that it can't do that to a merged range?

I can't think of any reason this wouldn't work.

PM me if you want to email the sheet to me.

 

[liquidmettle]

Here is a link to download it from dropbox.

There are some activex controls and other macros (the latter I also got help on). I erased all but the two sheets in question (so there will be a lot of ref errors, but as far as getting the macro to erase the set area, I think you should still be able to do so.

Let me know if you need anything else from my end.

Also, thanks so much!

https://www.dropbox.com/s/kvct9u83syjhybi/Macro Help.xlsm


Rick,

I am not certain how I would combine your code into the code already designed to make sure the sheet references are correct.