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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920