Button to clear color and content of cells

[the_observer]

Hi all.

My question this time ,regards to the creation of a button.

What i would like to do is to have a button somewhere in excel sheet which when i press it, it will clear specific cells from content and from color.

Can this be accomplished in Excel 2003?

Up to now i have found this post : http://chandoo.org/forum/threads/vba.9399/#post-53833
but it is for clearing all cells and does not refer to color.

Thank you for your time reading this.

Regards,
the_observer.

 

[Aurelie]

Hi the_observer,

I am not yet a super pro with VBA, but I think that what you want to do is relatively simple. You can copy the following code in a module:

Option Explicit
Sub ClearCell()
   
    Selection.Clear
 
End Sub

To create the button, you insert a shape and assign this macro by right clicking on the shape.
It should delete everything which are in the selected cells, including color, format, content, etc.

Is it what you were looking for?

Cheers,

 

[NARAYANK991]

Hi ,

The Clear method of a range clears the range of everything , both data and formatting ; the ClearContents method clears a range of only its data.

Narayan

 

[the_observer]

Thank you for your answers.
They give me much wisdom.

In aurelie's answer , what should i put/ how should i syntax the Selection tobe e.g. specific cells of 2 rows?

E.g. lets say i would like to clear only cells of column B and D . For B lets say 3 to 7 and for D lets say 12 to 20.

I don't know how to syntax this example selection.

Thank you for your time reading this

Regards ,
the_observer.

 

[Smallman]

Hi TO

Give the Selection a wide birth. Just tell VBA what to clear and clear it

Sub ClrRange()
Dim rng As Range
  Set rng = Union(Range("B3:B7"), Range("D12:D20"))
  rng.Clear
End Sub

Take care


Smallman

 

[Aurelie]

Hi the_observer,

It depends on what you want to do exactly. If you want to delete the content and format always of the same cells or ranges of cell, then the solution of Smallman is what you are looking for.

If you need to be more flexible and the ranges change depending on whatever criteria, you just select all the cells you want to clear, and click the button you created or run the macro from the developer tab.

Hope it helps,

 

[the_observer]

Thank you all for your answers.

Smallman's post did the job

Regards,
the_observer.

 

[the_observer]

Hi again.

It seems that

rng.Clear

although it does what i asked for, it is not good for my specific excel because it completely delete the formatting and data.

So i decided to replace it with

rng.ClearContents

so it deletes only the data as Narayak suggested in previous post.

Now what remains to achieve the .Clear command but without deleting the formatting is to delete the fill color of the cells or to say it better ,to insert no fill color in the cells.

Can this be done?

Yes it can.
Now allow me to answer my own question in case someone will need it and so i will self decept that i give something back to the forum and not only ask

So since i am not lazy i searched around and found that clearing the fill color can be done with this:

rng.Interior.ColorIndex = xlNone

So the final vb code from smallman for clearing ONLY the data and removing the fill color of the cells without deleting the rest of the formatting would be:

Sub ClrRange()
Dim rng As Range
  Set rng = Union(Range("B3:B7"), Range("D12:D20"))
  rng.ClearContents
  rng.Interior.ColorIndex = xlNone
End Sub

Thank you for your time reading this.

Regards,
the_observer.