Hello, Excellers welcome back to another #MacroMonday #Excel blog post in my 2020 Excel and VBA series.  Do you do the same thing over and over again in Excel?.  DO you want to get automating with some simple macros and VBA?.  Well, here is a great simple macro to get started on that journey.  Today I will show you how to delete cell content with a simple Excel VBA macro.  If you repeat the same actions over and over again on a regular basis then creating some simple code to reuse will save a tonne of time.

What Does The Macro Do?

This macro will delete cell content from a specified range of cells.  It can also be easily changed to simply clear a user-selected range of cells.  

Starting The Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.

• Either by hitting ALT +F11 or
• selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.

Before we begin to write any code you need to decide where to store the code. You have a choice.

1. To store your code either in your Personal Macro Workbook or
2. Store it in your current workbook.

What’s the difference?.  Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.  When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb.   If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.

• Creating and Updating Your Personal Macro Workbook
• Why Is My Personal Macro Workbook Not Loading Automatically?
• Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook  

 

Preparing To Write The Macro.  Clear Contents From A Specific Range Of Cells.

    We need to start off the process by inserting a New Module.  Do this by selecting the Personal.xlsbworkbook, then Insert Module.  Type Sub then the name of your macro.  In this example, I have called the macro  ClearCells  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines. [stextbox id=’info’] Sub ClearCells End Sub [/stextbox]

 

Declaring Variables.

We need to declare a variable for this macro. This ensures that Excel creates memory containers for these values.  In this example, we need to declare that the range of cells I want o clear the contents from is A1:A4. [stextbox id=’info’] Dim rng As Range Set rng = Range(“A1:A4”) [/stextbox]

 

Clearing Cell Contents.

The next part of the code simply clears the contents of the range of cells we have specified. [stextbox id=’info’] rng.Clear [/stextbox]  

 

Ending The Macro.

Finally, the code ends once all looping of cells has been completed with the “End Sub" piece of code.  This was already entered into the module for us when started the type the name of the macro. [stextbox id=’info’] End Sub [/stextbox]

 

Version 2 of the Macro.  Clear Contents From A User Selected Range Of Cells.

This next version of the macro will simply clear the cell contents from a user-selected range of cells.  The code is simple.    [stextbox id=’info’] Sub ClearCells Selection.Clear End Sub [/stextbox] This code will clear any cell contents of cells selected by the user.  What so you think about this simple Excel VBA  macro? to delete cell content in Excel.

 

 

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

 

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.