How to Clear Sheet Data with Excel VBA: Simple Macro Tutorial for Beginners

You are dealing with a large amount of data in Excel,I want to erase all the contents of this sheet..."Have you ever wondered why? Deletion of previous data is inevitable, especially in the process of updating data on a regular basis.

But manually selecting and deleting all cells is tedious. Furthermore,I want to delete just the data, or the formatting, or the entire row.The requirements vary depending on the situation, such as How can these be done efficiently and without error?

I have been helping companies improve the efficiency of their Excel operations for nearly 20 years. From that experience, I can say that "simple but frequent tasks" such as data deletion are the most important,The benefits of automation are great.That is to say. With the right macros, it is not uncommon for a task that used to take several minutes to complete to take only a few seconds.

In this article, the following steps will explain how to efficiently delete data from a sheet using Excel VBA.

1. Preparation: Basic settings for using macros
2. .ClearContents: How to delete data only
3. .Clear: How to delete data and formatting
4. .Delete: How to completely delete an entire row or column For each, we also provide code that can be copied and pasted for immediate use.

The benefits of acquiring the skills to delete sheet data with macros:

1. Greatly reduces the time required to delete data.
2. Reduce the risk of data loss due to deletion errors
3. Be able to select the appropriate deletion method for the situation
4. Learn how to use basic macros and apply them to other tasks

Data deletion may seem like a simple task, but it actually involves a lot of time and risk.
Macros can solve all these problems at once.
You may think, "Macros seem difficult," but the methods introduced in this article are easy to use, even for programming beginners.
With just a little learning, your Excel work may become dramatically more efficient.
Come, let's take the first step into the world of Excel macros together!

To delete the sheet itself, use theHow to delete a sheet by specifying the sheet name Excel(Excel) Macro(VBA).

advance preparation

The prerequisite is that the sheet on which the macro is executed and the sheet on which the deletion is actually performed are two different sheets.

The sheet to be executed is "Execute" and the sheet to be deleted is "Clear".

The following procedure is created as a preliminary preparation.

Sub Clear Sheet()


    Dim tsh As Worksheet: Set tsh = Sheets("Clear") ' Store "clear" sheets in "tsh

    'List the deletion code here.

End Sub

ClearContents(Delete data only)

The image is like selecting all the cells on the sheet and pressing the delete key to erase them.

Sub Clear Sheet()

    Dim tsh As Worksheet: Set tsh = Sheets("Clear")

    tsh.Cells.ClearContents

End Sub

It is like a delete key, so formatting, row and column width settings, etc. are retained, but only the data is deleted.

This is useful if you want to delete data but keep the formatting.

Clear(delete data and formatting)

ClearContents leaves formatting such as ruled lines and fills, but if you want to remove them as well, use this.

Sub Clear Sheet()

    Dim tsh As Worksheet: Set tsh = Sheets("Clear")

    tsh.Cells.Clear

End Sub

Not only the data, but even the formatting has disappeared, but the row and column widths remain intact.

This is useful if you want to remove data and formatting without changing the matrix size.

Delete(Delete all)

Sub Clear Sheet()

    Dim tsh As Worksheet: Set tsh = Sheets("Clear")

    tsh.Cells.Delete

End Sub

The image is as if all rows were selected and the entire row deleted.

So hidden rows and columns will disappear, as well as all formatting and fills. The matrix width and height settings will also be removed.

One weakness, however, is that if the auto-filter is on...

Only the parts that were hidden by the filter remained.

To avoid this phenomenon of remaining data, the filter is displayed in its entirety.

ShowAllData will show all data if it is filtered, but if it is not filtered, an error will occur. Therefore, it is temporarily set to error ignore mode.

Sub Clear Sheet()

    Dim tsh As Worksheet: Set tsh = Sheets("Clear")

    On Error Resume Next 'Error ignore mode
    ShowAllData 'Show all rows if any are hidden by the filter
    On Error GoTo 0 Error Ignore Mode Cancel

    tsh.Cells.Delete

End Sub

This allowed us to completely initialize the system.

There are many other commands depending on what you want to clear, but I think these typical ones should be sufficient. I rarely use anything other than these commands.

Use it according to the level you want to clear.