Excel VBA: Clear Contents of Multiple Ranges

VBA & macros · Updated November 22, 2026

To clear the contents of multiple ranges in Excel VBA, use the Range.ClearContents method on each range, or combine them into a single Range object using the Union method. For example: Union(Range("A1:A10"), Range("C1:C10")).ClearContents. This removes only the cell values, leaving formatting, comments, and data validation intact.

Why ClearContents Instead of Clear or Delete?

Excel VBA offers several ways to remove data, and each behaves differently. ClearContents removes the values from cells but keeps the cell formatting (like colors, borders, and number formats). Clear (or Range.Clear) removes both contents and formatting. Delete shifts surrounding cells up or left, which can break formulas and layout. For most tasks where you want to reset data without disturbing the worksheet’s appearance, ClearContents is the safest choice.

Basic Syntax and Simple Examples

The core method is straightforward. You reference a range and call ClearContents. Here are a few common patterns:

  • Clear a single contiguous range: Range("A1:D10").ClearContents
  • Clear a named range: Range("MyRange").ClearContents
  • Clear an entire column: Columns("B").ClearContents
  • Clear an entire row: Rows(5).ClearContents

These work on the active worksheet. To specify a particular sheet, qualify the range with the worksheet object, for example: Worksheets("Data").Range("A1:D10").ClearContents. This prevents errors if the wrong sheet is active.

Clearing Multiple Ranges at Once

When you need to clear several non-contiguous ranges, you have two main options. The first is to call ClearContents on each range separately:

Range("A1:A10").ClearContents
Range("C1:C10").ClearContents
Range("E1:E10").ClearContents

The second, more efficient method uses the Union function to combine the ranges into a single Range object and then clear it once:

Dim combinedRanges As Range
Set combinedRanges = Union(Range("A1:A10"), Range("C1:C10"), Range("E1:E10"))
combinedRanges.ClearContents

Using Union is faster because it performs a single operation on the worksheet. It also makes your code cleaner when you have many ranges. Note that Union can combine a maximum of 30 areas in older Excel versions; in modern Excel (365/2021) the limit is much higher, but it’s still good practice to keep the number reasonable.

Using Variables and Loops for Dynamic Ranges

Often the ranges you need to clear are not known in advance. You can use variables to store row numbers or column letters, and then build the range strings dynamically. For example, to clear columns A, C, and E from row 1 to the last used row in column A:

Dim lastRow As Long
Dim rng As Range

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Union(Range("A1:A" & lastRow), Range("C1:C" & lastRow), Range("E1:E" & lastRow))
rng.ClearContents

If you have a list of column letters in an array, you can loop through them:

Dim cols As Variant
Dim i As Integer
Dim clearRng As Range

cols = Array("A", "C", "E", "G")
For i = LBound(cols) To UBound(cols)
    If clearRng Is Nothing Then
        Set clearRng = Range(cols(i) & "1:" & cols(i) & "100")
    Else
        Set clearRng = Union(clearRng, Range(cols(i) & "1:" & cols(i) & "100"))
    End If
Next i
clearRng.ClearContents

This pattern is useful when the ranges are determined by user input or other calculations. Always declare your variables and consider using Option Explicit to avoid typos.

Common Pitfalls and How to Avoid Them

One frequent mistake is using Clear when you only want to remove values. This wipes out formatting, which can be jarring if you have conditional formatting or custom styles. Another pitfall is forgetting to qualify the worksheet. If you run a macro on the active sheet but the user has a different sheet selected, you might clear the wrong data. Always use Worksheets("YourSheetName") or ThisWorkbook.Worksheets("YourSheetName").

Also, be careful with End(xlUp) or End(xlDown) when determining the last row. If the column is completely empty, End(xlUp) from row 1 returns row 1, which might not be what you want. In such cases, check if the cell is empty first or use a more robust method like Cells.Find.

Finally, remember that ClearContents does not remove data validation. If you have dropdown lists in those cells, they remain. If you want to remove validation too, you’ll need to use Validation.Delete separately.

Performance Tips for Large Ranges

Clearing a massive range (e.g., an entire column of 1 million rows) can be slow if done cell by cell. The ClearContents method is already optimized, but you can speed things up further by disabling screen updating and automatic calculations while the macro runs:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Your clear code here

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Always reset these settings in a Finally block or after your code, even if an error occurs. Use error handling to ensure they are restored.

Alternatives: Clear vs. Delete vs. Value = “”

Besides ClearContents, you can set the range’s Value property to an empty string: Range("A1:A10").Value = "". This also clears only the contents. However, ClearContents is slightly faster and more explicit. Delete shifts cells, which can break formulas that reference the deleted cells. Clear removes formatting as well. For most data-reset scenarios, ClearContents is the best balance.

Frequently Asked Questions

Can I clear contents from multiple sheets at once?

Yes, loop through the worksheets and call ClearContents on each. For example: For Each ws In ThisWorkbook.Worksheets: ws.Range("A1:B10").ClearContents: Next ws. Be cautious if you only want to clear specific sheets; you can check the sheet name in the loop.

Does ClearContents remove comments or hyperlinks?

No. ClearContents only removes the cell’s value (text, numbers, dates, etc.). Comments, hyperlinks, and cell formatting remain. To remove comments, use Range.Comments.Delete or ClearComments (available in Excel 365). For hyperlinks, use Range.Hyperlinks.Delete.

How do I clear contents but keep formulas?

You cannot clear contents and keep formulas because the formula is the content. If you want to remove the formula’s result but keep the formula, that’s not possible—the formula recalculates. Instead, you might want to clear only certain cells that contain constants, but that requires checking each cell’s HasFormula property.

What if I clear a range that includes merged cells?

ClearContents works on merged cells, but it clears the entire merged area. If you only want to clear part of a merged cell, you must unmerge first. Otherwise, the whole merged region is cleared.

Is there a way to undo a ClearContents macro?

No. Macros that modify the worksheet cannot be undone with Ctrl+Z. That’s why it’s critical to save a copy of your workbook before running any macro that clears data. You can also add a confirmation prompt in your code using MsgBox to prevent accidental data loss.

Can I use ClearContents on an entire row or column without specifying the end?

Yes, you can use Rows(5).ClearContents or Columns("B").ClearContents. This clears the entire row or column, which may be more than you need but is sometimes acceptable. To limit to used range, combine with UsedRange or CurrentRegion.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.