How To Clear The Entire Sheet Or Specified Range In Excel?

MS Word Online Training

32 Lectures 2.5 hours

MS Excel Online Training

Best Seller

102 Lectures 10 hours

Advanced Excel (Power Query) Online Training

56 Lectures 5.5 hours

Do you often encounter too much data and spend a lot of time selecting and clearing Excel sheets?

If so, this tutorial will be helpful to you.

When analysing elaborative Excel worksheets, removing non-relevant information often makes it easier to study them. This tutorial explains how to clear an entire spreadsheet and a specified range in MS Excel using two simple methods

Method 1: How to Clear The Entire Sheet?

This is an easy procedure. If you want to clear the entire sheet, you can use a keyboard shortcut or from the corner cell. We learn how to do it in a few steps.

Step 1 − Select the top left corner cell between the rows and the column headers.

Step 2 − Right-click and choose "Delete" from the drop-down menu.

Following this, the spreadsheet would be clean of all data.

You can also use an alternative approach. Using the keyboard shortcut, press Control + A to select all cells in the spreadsheet. Then right-click and repeat step 2, and the result will be the same as above.

Method 2: How To Clear a Specified Range?

Suppose you want to clear only a particular set of cells in the spreadsheet. Doing so one by one could be cumbersome. However, using the Visual Basic for Applications (VBA), you can remove one or more specified ranges of cells from your spreadsheet.

The VBA is a computer programming language developed by Microsoft. It automates repetitive and routine tasks, simplifying the process of working out complex data sets. Let's understand how this works using the same data set mentioned earlier.

Step 1 − Press the Alt + F11 keys to open the Microsoft Excel Visual Basic Editor dialogue box.

Step 2 − Right-click on "Microsoft Excel Objects", and from the drop-down menu, select Insert → Module.

Step 3 − Paste the mentioned VBA code in the top right box after inserting the module to clear only a specified range of cells. Let's say we want to remove only the table with the range C8 to F12 highlighted in green. We modify the range in the code.

VBA Code to clear specific range of cells from Excel sheet −

Sub sbClearCells()
Range("C8:F12").Clear
End Sub


Step 4 − Press F5 on your keyboard or click the "Run Program" button to execute the code.

The resulting table will look like this.

Step 5 − If you want to remove multiple ranges of cells from your spreadsheet, you can modify the VBA code mentioned above and do it in one easy step.

Suppose you want to remove cells A3 to B5; cells D13 to F15; and cells B10 to C11. Enter the code in the Module window and repeat step 3.

Sub sbClearCells()
Range("A3:B5").Clear
Range("D13:F15").Clear
Range("B10:C11").Clear
End Sub


The VBA program will clear the selected ranges from the spreadsheet.

Conclusion

This tutorial demonstrated how to clear a specific cell range in Excel and an entire sheet. You can quickly and efficiently perform customised tasks with VBA code as well. With Excel tutorials, you can master and use the program in your work and everyday life.

Updated on 20-Sep-2022 07:37:40