Ways to Clear in VBA

Learn Excel from MrExcel Podcast, Episode 2068: Ways to Clear in VBA. 

I got a bit of mystery here, one comment left on one of my YouTube videos from Roger:  I want to clear text in a given array of cells but not the formatting.  And Roger says with the .ClearContents, it clears everything in the cells.  Is it possible to create a macro to clear only the text and generate the next invoice number?  So, you must have clear only the text from cells.  And there are several different ways to clear in VBA.  Let me switch over here to Alt+F11 where we take a look at all of them.  So there's .Clear which should clear everything: ClearComments, ClearContents, ClearFormats, ClearHyperlinks, ClearNotes and ClearOutline.

So, I've created some data here and you see that I'm going to each individual column and running the various different VBA methods.  Alright, so .Clear got rid of everything.  .ClearComments, you see there's a comment right here that hopefully will go away, yep.  .ClearContent .  I see that I didn't get rid of borders or the filled colors and we'll circle back to this one in a bit.  ClearFormats leaves the  numbers, gets rid of the formatting.  ClearHyperlinks, ahh, should have gotten rid of the hyperlink but it did not.  Interesting.  ClearNotes is like clear comments where our comments were called notes in the original version and then ClearOutline got rid of our outline for all of the rows, alright.  So, those are the- those are the options that we have. 

And personally, I like ClearContents because ClearContents gets rid of the numbers but leaves the formatting, alright?  Just type 1234, press Ctrl+Enter, and all of the superscript, strikethrough, fill color, number formatting, all of that stuff stays.  So in my opinion, ClearContents is the good one.  It's the one that keeps all your formatting there, all the borders there, but just gets rid of the Value.  So, I'm a bit perplexed with Roger’s question because it seems like ClearContent should be the one that's doing what he's doing.  Although, I wonder if what Roger really wants to do is get rid of the things that aren't formulas.  Alright so, here I'm going to do, show formulas with Ctrl and the grave accent.  And you see that we have contents are constants in B, C and G but then formulas in D, E and F.  I wonder if Roger is trying to get rid of the text but leave the formulas there, alright.  And if that's the situation then we want to go one step beyond this, alright, where we specify the entire range of B5 to G13.  And before we do the ClearContents, we want to say .SpecialCells(xlCellTypeConstants).  So in other words, if it's a formula, leave it alone.  When we run this macro you'll see that the constants go away but all the formulas are still there.  And now, I should have typed IF error to make them not appear as array but it keeps, the formula is there.  I'm not sure, I'm a bit perplexed with Roger’s question because to me it seems like ClearContent should be doing what it's doing, but there's all of the options.

Now, this book doesn't cover VBA but it covers a lot of things.  Brand new book just came out, Power Excel with MrExcel.  Click that “i” in the top-right hand corner to take a look at that.

Alright, so wrap-up of this episode: How to clear in VBA?  There's seven different methods available: Clear will clear everything; just the comments; keeps the formatting, clears the values and formulas; gets rid of just the formatting; gets rid of just the hyperlinks; a second way to clear comments because comments used to be called notes; ClearOutline gets rid of the group and outline groups; and then, there then we use special cells to select only constants or only formulas and then clear contents.  There.

Oh, hey, I want to thank Roger for sending that question and I want thank you for stopping by.  We'll see you next time for another netcast from MrExcel.