The fastest way to clear both formatting and contents in Excel VBA is to use the Clear method on a range, for example: Range("A1:C10").Clear. This removes cell values, formulas, formatting, comments, and hyperlinks in one step. If you need to clear only contents or only formatting separately, use ClearContents or ClearFormats respectively.
Why Clearing Formatting and Contents Together Matters
In Excel, a cell can hold several layers of data: the value or formula, number formatting, font styles, fill colors, borders, conditional formatting rules, data validation, comments, and hyperlinks. When you press the Delete key, Excel removes only the contents (values and formulas) but leaves formatting intact. That often leaves behind unwanted colors or borders that confuse users. The VBA Clear method is the equivalent of clicking the “Clear All” button on the Home tab, but it can be automated and applied to any range, worksheet, or even the entire workbook.
Understanding the Core Methods: Clear, ClearContents, ClearFormats
Excel VBA offers three main methods for clearing cells, each with a specific purpose:
Range.Clear– Removes everything: values, formulas, formatting, comments, hyperlinks, data validation, and conditional formatting. This is the most thorough option.Range.ClearContents– Removes only the cell values and formulas, but leaves formatting, comments, and data validation in place.Range.ClearFormats– Removes only the formatting (font, fill, borders, number format, conditional formatting), but leaves the values and formulas untouched.
For example, if you want to delete the contents of a table but keep its header formatting, use ClearContents. If you want to reset a range to default formatting but keep the numbers, use ClearFormats.
How to Write a Basic Clear Macro
To clear a specific range, open the VBA editor by pressing Alt+F11 (Windows) or Option+F11 (Mac). Insert a new module and type the following code:
Sub ClearRangeExample()
' Clears everything in A1:C10
Range("A1:C10").Clear
End Sub
To clear the entire worksheet, use Cells.Clear or ActiveSheet.Cells.Clear. To clear all worksheets in the workbook, loop through each sheet:
Sub ClearAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.Clear
Next ws
End Sub
Remember that macros cannot be undone with Ctrl+Z. Always save a copy of your workbook before running a macro that clears data.
Clearing Only Used Cells to Save Time
Clearing an entire worksheet with Cells.Clear can be slow if the worksheet has a large used range (e.g., formatting applied to entire columns). To clear only the cells that actually contain data or formatting, use the UsedRange property:
Sub ClearUsedRange()
ActiveSheet.UsedRange.Clear
End Sub
However, UsedRange can sometimes include cells that appear blank but have formatting. To reset the used range completely, you can clear the entire sheet, but that is often unnecessary. A more precise approach is to find the last used row and column and clear only that range:
Sub ClearDataRegion()
Dim lastRow As Long, lastCol As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), .Cells(lastRow, lastCol)).Clear
End With
End Sub
This clears only the rectangular region that contains data, leaving the rest of the sheet untouched.
Clearing Specific Types of Formatting
Sometimes you want to clear only certain formatting elements, like number formats or conditional formatting, without wiping out everything. Use these methods:
Range.NumberFormat = "General"– Resets number formatting to default.Range.Font.ColorIndex = xlAutomatic– Resets font color to automatic.Range.Interior.ColorIndex = xlNone– Removes fill color.Range.Borders.LineStyle = xlNone– Removes all borders.Range.FormatConditions.Delete– Removes conditional formatting rules.Range.Validation.Delete– Removes data validation.Range.Comments.Delete– Removes comments (useRange.ClearCommentsin Excel 2010 and later).
For example, to clear only the fill color from A1:A10 while keeping everything else:
Sub ClearFillOnly()
Range("A1:A10").Interior.ColorIndex = xlNone
End Sub
Common Problems and Misconceptions
One common mistake is using Delete instead of Clear. The Delete method shifts cells and can disrupt the layout. Always use Clear unless you intentionally want to delete cells and shift others up or left.
Another issue is that Clear does not remove cell comments in some older Excel versions. In Excel 2010 and later, Clear does remove comments, but if you’re using an older version, you may need to use ClearComments separately.
Also, clearing a range that contains merged cells can cause an error. If you try to clear a range that includes merged cells, Excel may throw a runtime error. To avoid this, unmerge cells first, or use error handling:
Sub ClearWithMergedCells()
On Error Resume Next
Range("A1:C10").Clear
On Error GoTo 0
End Sub
But note that On Error Resume Next will silently skip the clear if it fails, so it’s better to unmerge explicitly if you know there are merged cells.
Alternatives to VBA: Built-in Excel Features
If you prefer not to use VBA, Excel has built-in commands that do the same thing:
- Select the range, then go to Home > Editing > Clear > Clear All (clears everything).
- Use Clear Contents to remove values only.
- Use Clear Formats to remove formatting only.
You can also use the shortcut Alt+H, E, A (Windows) to clear all. On Mac, the ribbon path is the same, but keyboard shortcuts differ.
For a one-time cleanup, these built-in features are simpler and safer because they can be undone with Ctrl+Z. VBA is better when you need to repeat the process across many sheets or workbooks, or when you want to automate a cleanup as part of a larger macro.
Frequently Asked Questions
Can I clear formatting and contents with a single keyboard shortcut? Yes, select the range and press Alt+H, E, A (Windows) to clear all. On Mac, you can use the ribbon menu: Home > Clear > Clear All. There is no single-key shortcut, but this two-key sequence is quick.
Does ClearContents remove comments? No. ClearContents only removes values and formulas. Comments are considered part of the cell’s annotation, not its content. To remove comments, use ClearComments or Clear.
How do I clear formatting but keep the values in VBA? Use Range.ClearFormats. For example, Range("A1:A10").ClearFormats will remove all formatting but leave the numbers and text intact.
What is the difference between Delete and Clear in VBA? Delete removes the cells themselves and shifts neighboring cells to fill the gap, which can change the layout. Clear removes the contents and formatting but leaves the cells in place, so the layout remains unchanged.
Can I clear formatting on multiple sheets at once? Yes, loop through the sheets as shown earlier. Alternatively, group the sheets by selecting them (Ctrl+click each tab) and run a macro that applies to ActiveSheet, but looping is more reliable.
Will clearing a range remove data validation dropdowns? Yes, Clear removes data validation. If you want to keep the dropdowns, use ClearContents instead, which does not affect validation.
Related
- Excel Sheet Template for Budget: How to Build One
- Excel Show Weekday for Date: 4 Easy Ways
- Excel Sheet for Attendance: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find a Row With a Specific Value in an Excel SheetTo find a row with a specific value in Excel VBA, use the Find method on the relevant…
- Excel VBA Function Multiple Outputs: Practical MethodsYes. An Excel VBA function can provide multiple outputs by returning an array, or VBA code can use…
- Excel VBA Export Chart: Image, PDF, and OptionsTo export an embedded Excel chart with VBA as a PNG, call its Chart.Export method and specify a…
- Excel VBA Option Explicit: What It Does and How to Use ItOption Explicit is a VBA module setting that requires you to declare variables before using them. Put it…
- Excel VBA Function vs Sub: Differences and ExamplesIn Excel VBA, a Function calculates and returns a value, while a Sub performs an action and does…
- Excel VBA: Delete Rows Based on CriteriaTo delete Excel rows that meet a condition, loop through the relevant rows from bottom to top and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.