With VBA, we can manipulate our data in virtually any way imaginable. It can be useful for both creating and deleting work.
In the example below, we will show how to set a certain range to be empty with VBA.
Using Clear to Empty the Range
The first way to define an empty range with VBA is to run a script that will empty the values and formatting of the desired range. To show this, we will first open the VBA by clicking ALT + F11. After that, we will right-click in the left side window and choose Insert >> Module:
The new window will appear on the right window. For our example, we will write in sales figures in cells A2:A10:
To clear this data, and the formatting as well (if we had it), this is the code that we need to insert into our VBA:
|
1 2 3 |
Sub ClearingRange() Range("A2:A10").Clear End Sub |
When we execute this code by pressing F5 on the keyboard (while in the module), we will be left only with the header:
Setting Range to Empty
There is an alternative to this approach, and with it, we are going to allow the user to select the range he wants to set to empty.
We will insert the code right below our first one, and it will go like this:
|
1 2 3 4 5 6 7 8 9 |
Sub SettingRangeToEmpty() Dim rng As Range Dim j As Range Set rng = Application.InputBox(Title:="Empty the Range", _ Prompt:="Select a range that you want To Set To empty", Type:=8) For Each j In rng.Cells j.Value = "" Next j End Sub |
This is what the codes look like in the module:
The first part declares two variables: rng and j as range, and then it sets rng to be equal to whatever range the user chooses.
For the last part, For Next Loop is used to make sure that every single cell (j variable) located in the selected range is changed to empty, or “”.
We will insert some random figures in the range C2:F5:
We will execute the code by going back to the module and pressing F5 in our code. Once we do, the following message will appear on our screen:
We can either input the range, or select it, however, we want. We will select the desired range:
And then press OK. Once we do, our range will be deleted:
