Set Range to Empty in VBA

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:

Graphical user interface, application

Description automatically generated

The new window will appear on the right window. For our example, we will write in sales figures in cells A2:A10:

Text

Description automatically generated

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:

Sub ClearingRange()
Range("A2:A10").Clear
End Sub

When we execute this code by pressing F5 on the keyboard (while being in the module), we will be left only with the header:

Table

Description automatically generated

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:

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:

Graphical user interface, text, application, email

Description automatically generated

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:

Timeline

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

We can either input the range, or select it, however, we want. We will select the desired range:

Graphical user interface, application

Description automatically generated

And then press OK. Once we do, our range will be deleted:

Posted in vba