Excel VBA Clear Row Contents: 4 Ways

VBA & macros · Updated November 22, 2026

To clear row contents in Excel VBA, use the Range.ClearContents method on the entire row, for example Rows(5).ClearContents clears row 5. For a partial row, use Range("A5:F5").ClearContents. This removes values and text but leaves formatting, comments, and hyperlinks intact.

Why ClearContents Instead of Delete or Clear

Excel has three similar but distinct actions. ClearContents removes only the cell’s value (the data you typed or calculated). Clear (or Range.Clear in VBA) removes both the value and all formatting, including fill colors, borders, and number formats. Delete shifts surrounding cells up or left, which changes the structure of your worksheet. If you only want to empty a row for reuse, ClearContents is the safe, non-destructive choice. If you also want to reset formats, use Clear. If you want to remove the row entirely and shift data up, use Rows(5).Delete.

Basic VBA Syntax for Clearing a Row

Open the VBA editor with Alt+F11 (or Option+F11 on Mac). Insert a module and write a sub like this:

Sub ClearRowFive()
    ' Clears values in row 5 only
    Rows(5).ClearContents
End Sub

To clear a specific range within a row, specify the range:

Sub ClearRangeInRow()
    ' Clears columns A through F in row 10
    Range("A10:F10").ClearContents
End Sub

You can also use Cells to build a range dynamically. For example, to clear from column 1 to column 10 in row 3:

Sub ClearDynamicRange()
    Dim r As Long
    r = 3
    Range(Cells(r, 1), Cells(r, 10)).ClearContents
End Sub

Remember to save a copy of your workbook before running any macro, because ClearContents cannot be undone with Ctrl+Z.

Clearing the Active Row or a Row Based on a Condition

Often you want to clear the row where the cursor is, or clear rows that meet a certain condition. For the active cell’s row:

Sub ClearActiveRow()
    ActiveCell.EntireRow.ClearContents
End Sub

To clear rows where a specific column has a value (for example, clear all rows where column B is empty):

Sub ClearRowsWithEmptyColumnB()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 1 Step -1
        If IsEmpty(Cells(i, 2)) Then
            Rows(i).ClearContents
        End If
    Next i
End Sub

Looping from bottom to top (Step -1) is important when you might delete rows later; for clearing, it doesn’t matter, but it’s a safe habit.

Clearing Multiple Non-Adjacent Rows

If you need to clear rows 2, 5, and 9, you can use the Union method:

Sub ClearMultipleRows()
    Dim rng As Range
    Set rng = Union(Rows(2), Rows(5), Rows(9))
    rng.ClearContents
End Sub

If you have a list of row numbers in a column (say column D), you can loop through that list:

Sub ClearRowsFromList()
    Dim cell As Range
    For Each cell In Range("D1:D10")
        If IsNumeric(cell.Value) And cell.Value > 0 Then
            Rows(cell.Value).ClearContents
        End If
    Next cell
End Sub

How to Run the Macro and Where to Put It

You can run a macro in several ways:

  • Press Alt+F8 to open the Macro dialog, select the macro, and click Run.
  • Assign the macro to a button (Form Control) on the sheet.
  • Call it from another macro or from an event like Worksheet_Change.

If you want the macro to run automatically when a cell changes, place code in the worksheet’s code module (double-click the sheet name in the Project Explorer). For example, to clear row 5 when you type “clear” in cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Target.Value = "clear" Then
        Rows(5).ClearContents
        Target.ClearContents
    End If
End Sub

Note that event code must be in the sheet module, not a standard module.

Common Problems and How to Avoid Them

  • Clearing the wrong row: Hard-coded row numbers break if your data moves. Use ActiveCell.Row or a variable.
  • Clearing merged cells: If a merged cell spans multiple rows, ClearContents on one row may not clear the merged value. Use MergeArea.ClearContents on the merged range.
  • Clearing formulas vs. values: ClearContents removes the formula too, leaving a blank cell. If you want to keep the formula but remove the result, you can’t—clearing removes the formula. Instead, you might set Calculation to manual.
  • Undoing: Macros cannot be undone with Ctrl+Z. Always save a backup copy before running.
  • Performance: Looping row-by-row on large datasets is slow. Instead, use Range("A1:Z1000").ClearContents or use AutoFilter to filter and clear visible rows at once.

Comparison: ClearContents vs. Clear vs. Delete

Here’s a quick reference:

  • ClearContents – removes values and text, keeps formatting, comments, and hyperlinks.
  • Clear – removes everything: values, formatting, comments, hyperlinks.
  • Delete – removes the entire row and shifts up remaining rows, changing the worksheet layout.

For most data-clearing tasks, ClearContents is the right choice because it preserves the visual design of your sheet.

Frequently Asked Questions

How do I clear a row without deleting the formulas?

You cannot clear a cell’s value without removing the formula, because the formula is the value. If you want to clear the displayed result but keep the formula, you could set the cell’s number format to hide the value, but that’s not a true clear. Alternatively, you can copy the formula to a temporary location, clear, then paste it back, but that’s complex. Usually, you clear the row and then re-enter formulas if needed.

Can I clear a row based on a cell value in that row?

Yes. Use a loop or AutoFilter. For example, to clear all rows where column C equals “Done”, you can filter and then clear the visible rows. A simple loop is easier to understand for small datasets.

Does ClearContents work on filtered rows only?

No, ClearContents on a range clears all cells in that range, including hidden or filtered ones. To clear only visible filtered rows, use SpecialCells(xlCellTypeVisible).

What is the shortcut to clear row contents without VBA?

Select the entire row by clicking the row number, then press Delete on your keyboard. That clears the contents (but not formatting). On Mac, it’s also the Delete key.

How do I clear a row in Excel for the web using VBA?

Excel for the web does not support VBA macros. You can only use VBA in the desktop versions (Windows and Mac). For the web, you’d need to use Office Scripts or manually clear the row.

Can I undo a VBA clear?

No. Undo (Ctrl+Z) does not work after running a macro. That’s why it’s critical to save a backup copy of your workbook before running any clearing macro.

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.