Hello Excellers and welcome to another Excel #Excel tips #MacroMonday bog post in my 2020 macro series. Today I want to share with you a simple macro. This small bit of code will clear all cells with values that are greater than zero. I used this macro last week to clear a really large data set of all values that were over zero. I only needed the zero values to left as I was looking for gaps in my data. In this case zero sales.
Preparing To Write 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. You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that 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.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting 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 the macro LeaveZeros. 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 to clear our cells.
[stextbox id=’info’]
Sub LeaveZeros
End Sub
[/stextbox]
Turn Off Screen Updating
By turning off screen updating we avoid the screen flashing or flickering as the Excel executes the VBA code. Any time a change occurs on your Excel worksheet, the screen will update.
[stextbox id=’info’]
Application.ScreenUpdating = False
[/stextbox]
Declaring Variables
The next step in writing our macro is to declare some variables. This ensures that Excel creates a memory container for these values. In this example, there is only one variable to declare. We use the Range Object to represent the user-selected range and set that range as the selection.
[stextbox id=’info’]
Dim myRange As Range
Set myRange = Selection
[/stextbox]
Using The For Each Loop
Next, we instruct Excel to use the For Each Looping command. We start with For Each and instruct Excel to loop through all cells in the myRange variable. If the cell contents are greater than zero then the values will be cleared. This looping continues until all cells in the selected range have been analyzed.
[stextbox id=’info’]
For Each cell In myRange
If cell > 0 Then cell.ClearContents
Next cell
[/stextbox]
Turn Screen Updating Back On
We now return the screen updating to back on. This resumes normal Excel behaviour for changes on the worksheet.
[stextbox id=’info’]
Application.ScreenUpdating = True
[/stextbox]
Ending The Excel Macro
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]
More Related #MacroMonday Excel Blog Posts
Excel Tutorial- Add Your Own Macros To The Excel Ribbon Tab
Can’t Remember Where Your File Is Saved?. Cell Function To The Rescue
If you want more 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.
