Excel VBA Clearing: Ways to Clear in VBA - Episode 2068

3,894 views · Published 29 March 2017 · 4:00 · Indexed 30 October 2026

Channel: MrExcel.com · 2017 · Education

Watch on YouTube

Microsoft Excel Tutorial: Using Excel VBA to Clear Text but Not Formatting.

Welcome to another episode of the MrExcel Podcast, where we dive into all things Excel. In this episode, we will be discussing the various ways to clear data in VBA. This topic was inspired by a comment left by one of our viewers, Roger, who asked about clearing text in a given array of cells without affecting the formatting. So, let's explore the different options available in VBA to achieve this.

First, let's take a look at the .Clear method, which clears everything in the cells including comments, contents, formats, hyperlinks, notes, and outlines. Next, we have .ClearComments, which specifically targets comments and removes them from the cells. Then, there's .ClearContents, which clears the values and formulas but leaves the formatting intact. .ClearFormats gets rid of the formatting while keeping the numbers, and .ClearHyperlinks removes only the hyperlinks. .ClearNotes is similar to .ClearComments, but it targets notes instead. Lastly, .ClearOutline removes the group and outline groups from the cells.

Out of all these options, I personally prefer using .ClearContents as it gets rid of the values but keeps the formatting. This means that all your borders, colors, and other formatting will remain intact while the data is cleared. However, I'm a bit perplexed by Roger's question as it seems like .ClearContents should be the solution he's looking for. But, I wonder if he wants to clear only the text that is not a formula. In that case, we can use the .SpecialCells method to select only constants or formulas and then use .ClearContents to clear the data.

If you're interested in learning more about Excel and its various features, I highly recommend checking out my new book, "Power Excel with MrExcel". It covers a wide range of topics and is a great resource for anyone looking to improve their Excel skills. You can find the link to the book in the top-right corner of this video.

To wrap up, there are seven different methods available in VBA to clear data in cells. Each method serves a specific purpose, so it's important to understand their differences and use the one that best fits your needs. Thank you to Roger for the question and thank you for tuning in to this episode of the MrExcel Podcast. Don't forget to hit that like button and subscribe to our channel for more Excel tips and tricks. We'll see you next time for another informative netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#excelvba

This video answers these common search terms:
how to clear macros in excel
how to set a macro that will clear cells excel
how to make a macro to clear data in excel
how to create a macro in excel to clear data
how to create a macro to clear cells in excel
how to assign "clear" macro in excel
how to create a clear range of cells macro in excel
how to create a clear specific cells macro in excel
how to clear contents in vba excel
how to clear cells in excel vba
how to clear a specific cells vba excel
how to clear a specific range vba excel
how to clear a column in excel vba
how to clear contents in excel vba
how to clear excel sheet using vba
how to clear range of cells in excel vba
how to clear the cells in excel using vba
how to clear a range of cells in excel vba
how to clear a worksheet in excel usiing vba

Table of Contents:
(00:00) Ways to Clear in VBA
(00:14) Overview of different ways to clear in VBA
(00:33) Demonstration of different VBA methods for clearing
(01:37) Personal preference for using ClearContents
(01:59) Possible confusion with Roger's question
(02:31) Alternative method for clearing only constants or formulas
(03:13) Advertisement for new book, Power Excel with MrExcel
(03:23) Summary of different methods for clearing in VBA
(03:33) Clicking Like really helps the algorithm

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

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153896/

More from this channel