Roger wants to clear text but not formatting in his VBA macro. This episode looks at all the ways to clear.
How to Clear in VBA
There are 7 different methods available
.Clear clears everything
.ClearComments clears comments
.ClearContents keeps the formatting and clears values & formulas
.ClearFormats clears the formatting
.ClearHyperlinks clears hyperlinks
.ClearNotes clears comments (Comments used to be called Notes)
.ClearOutline gets rid of group & outline groups
.SpecialCells can be used to select only constants or only formulas
How to Clear in VBA
There are 7 different methods available
.Clear clears everything
.ClearComments clears comments
.ClearContents keeps the formatting and clears values & formulas
.ClearFormats clears the formatting
.ClearHyperlinks clears hyperlinks
.ClearNotes clears comments (Comments used to be called Notes)
.ClearOutline gets rid of group & outline groups
.SpecialCells can be used to select only constants or only formulas
Transcript of the video:
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, he wants to 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.
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, he wants to 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.
