VBA & Macro Problems

Clear Excel Worksheet Contents with VBA Without Removing Drop-Down Lists

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Clear worksheet data with a VBA macro while keeping drop-down lists and conditional formatting.

Clear Excel Worksheet Contents with VBA Without Removing Drop-Down Lists
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro needs to reset worksheet cells without removing their data-validation rules or formatting.
Observed behavior
Range.Clear removes cell contents along with data-validation drop-down lists and conditional formatting.
Before you start

Identify the worksheet and cells that should be cleared. ClearContents removes formulas as well as values, so exclude headers, formulas, or other cells you need to keep.

Solution 1Recommended

Replace Range.Clear with ClearContents in the VBA macro

Use ClearContents to remove values and formulas while preserving data validation and formatting.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. Open the module containing the macro.

2
Change the clear method

Find the Range.Clear statement and replace Clear with ClearContents. For example, use Worksheets("Sheet1").UsedRange.ClearContents to clear the used range on Sheet1.

3
Limit the range if needed

To keep cells outside the input area, specify a range instead, such as Worksheets("Sheet1").Range("B2:B100").ClearContents. Replace the sheet name and cell addresses with your own.

4
Run and check the macro

Run the macro from Developer > Macros, then check the cleared cells to confirm that their drop-down lists and conditional formatting remain.

Replace Range.Clear with ClearContents in the VBA macro
UsedRange may include more than input cells: Clearing the entire used range also removes headers and formulas within it. Choose a narrower range when those cells must remain.
Clear spreadsheet contents in WPS

Clear Cells Without Removing Drop-Down Lists in WPS Spreadsheets

For a manual reset, use WPS Spreadsheets to clear selected cell contents while keeping their formatting and data-validation rules.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 2. Select the input cells: On the worksheet, select the cells whose values and formulas you want to remove, excluding any headers or formulas you need to keep.
  3. 3. Clear the contents: On the Home tab, open Clear and choose Clear Contents. Menu labels may vary by version.
  4. 4. Check and save: Click a cleared cell to check its drop-down list, then select File > Save to save the workbook.
Clear selected values and formulas while retaining drop-down listsOpen and edit Microsoft Excel XLSX and XLSM filesUse a familiar spreadsheet interface to select and reset input ranges
microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Clear and ClearContents in Excel VBA?

Range.Clear removes contents and formatting, including data validation. Range.ClearContents removes values and formulas while preserving formatting and data-validation rules.

Will ClearContents delete formulas?

Yes. ClearContents removes formulas as well as entered values. Specify only the input cells in your Range statement if formulas must remain.

How do I clear one column without affecting the rest of the sheet?

Specify the desired cells in the macro, for example Worksheets("Sheet1").Range("B2:B100").ClearContents. Change the sheet name and range to match your workbook.

Does ClearContents preserve conditional formatting?

Yes. ClearContents preserves conditional-formatting rules, although their visible appearance may change when the values those rules depend on are cleared.