For successful VBA programming and enjoying the process, I always recommend doing minor changes in your mindset. What I like to call – think "Like Excel".

What does think "Like Excel" mean?

Let me explain to you with an example.
Imagine you want to delete the content in one specific cell, what you would normally do would be select the cell with your mouse and click Delete.

However, when you think "Like Excel" you need to define step by step what you expect your VBA project to do.

How to clear content in Excel VBA

If as part of your project in VBA you need to delete content in some cells, columns or even the whole range, I bring you the 5 ways to effectively clear the content of your excel sheet.

Clear content in one cell Excel VBA

Starting with the basics,

if you want to clear the content of one specific cell – select the cell you want to clear and delete the content. In this example, we are deleting the content of cell A1.

If you would like to delete the content of cell C2, you would just replace the code A1 with C2

Sub MisterPaton_clearonecell ()
'Select the cell that you want to clear in this case cell A1
    Range("A1").Select
'Clear the content of the cell
    Selection.ClearContents
'Move to any other cell in this case cell A2
    Range("A2").Select    
End Sub

Clear content in one column Excel VBA

Taking it one step further, let’s imagine instead of the content of a cell, we want to remove the content of a whole column. We would select the column and clear the content.

In this example, we are removing the content of column A (in the code: A:A), if we would like to remove the content of column C (we would replace A:A with C:C)

See below the VBA code ready to copy-paste – with explanations - into your VBA module:

Sub MisterPaton_clearonecolumn()
'Select the column that you want to clear in this case column A
    Columns("A:A").Select
'Clear the conect of the column
    Selection.ClearContents
'Move to any other cell in this case cell A2
    Range("A2").Select
End Sub

Clear content in one row Excel VBA

In the same way, you can remove the content of a column, you can remove the content of a row too.

In this example, we are removing the content of row 1 (in the code: 1:1), if we would like to remove the content of row 3 (we would replace 1:1 by 3:3)

See below the VBA code ready to copy-paste – with explanations - into your VBA module:

Sub MisterPaton_clearonerow()
'Select the row that you want to clear in this case row 1
    Rows("1:1").Select
'Clear the conect of the row
    Selection.ClearContents
'Move to any other cell in this case cell A2
    Range("A2").Select
End Sub

Clear content in whole sheet Excel VBA

In case you want to clear the whole content of the sheet, you can remove it by selecting the whole range and clearing the content.

See below the VBA code ready to copy-paste – with explanations - into your VBA module:

Sub MisterPaton_clearrange()
'Select all the cells in the sheet
    Cells.Select
'Clear the content
    Selection.ClearContents
'Move to any other cell in this case cell A2
    Range("A2").Select
End Sub

Clear content in customize range Excel VBA

If you want to customize a specific range that you would like to delete, you can do it by recording your steps in Excel - and the code will be generated (alt + F11 to access the code)

How to do it:

1. Go to Developer tab
2. Click on Record Macro
3. Click OK on pop up window

Go to Developer tab

The developer tab is not displayed by default in Excel – if you have it visible great.

If not, no worries I got you covered.
Click here if you want to learn how to add a Developer to your Ribbon.

Locate Record Macro (on the upper left side of the screen) and click there

The following picture will appear. Set up the name of your Macro in this case "Macro1"and click OK.

Developer Tab excel

How to show the Developer Tab in Excel

On the File Tab, go to Options > Customize Ribbon

Under the Customize Ribbon, find Developer and move it to the right side

With these steps, you will have your developer tab visible in SAP ✅