Hello Excellers and welcome back to another #Excel and #MacroMonday tip in my 2019 series. Today let’s take a look at how to create a really handy Excel macro to clear the contents of the same cells from multiple worksheets. So let’s get coding.
Where To Store The Macro.
First, we need to decide where to store our macro. We then have a choice. To store your code either in your Personal Macro Workbook or 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.
Learn More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -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.
We need to start off the macro 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 it 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]
Setting The Range Of Cells To Clear.
First, we need to set the Worksheets and the Range of cells we want to clear. In this example, it is the worksheets named simply 1 and 2 and the whole of Column A. We specify that that only worksheets 1 and 2 are to be cleared. If we did not specify this then Column A of the current worksheet selected or active worksheet would be cleared. We just need to enclose the name of the worksheets in parentheses and quotes as well as the range of cells.
Using Clear.Contents Method.
[stextbox id=’info’]
Worksheets(“1”).Range(“A:A”).ClearContents
Worksheets(“2”).Range(“A:A”).ClearContents
[/stextbox]
Ending The Macro.
Finally, once all of the selected range has been checked for negative values the code finally ends. This instruction was already entered into the module for us when started the type the name of the Excel macro. You now can easily clear cell contents by running your simple macro.
[stextbox id=’info’]
End Sub
[/stextbox]
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.