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

 

[Rick Rothstein]

Rick,

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

Sorry, I should have included that for you (I colored the addition in blue so you can see what I changed)...

Sub ClearCellsSomeOfWhichAreMerged()
  Dim Index As Long, Cell As Range
  Index = 18
  Do While Index <= 166
    Set Cell = [COLOR=#0000FF][B]Sheets("Action Plan").[/B][/COLOR]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]

The code from post #8 works just fine for me.

 

[liquidmettle]

Jon, I retried that code and it does indeed work for me now too. I re-assigned the macro to the button before I hit it this time. I'm to go ahead and assume it was still trying to run some errant version of the code (I didn't think to re-assign it as I assumed it would run the new code under the same macro name).

Either way I want to thank you tremendously for sticking with it!

Thank you as well Rick even though I did not use your code.

Both of you Rock for being willing and to help and following through.

 

[Jonmo1]

I think you might have still used the code from Post #6 (before expanding it to 3 columns)


anyway, glad to hlep, thanks for the feedback.