Clear Excel Worksheet Contents with VBA Without Removing Drop-Down Lists
Question details
Clear worksheet data with a VBA macro while keeping drop-down lists and conditional formatting.

- 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.
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.
Replace Range.Clear with ClearContents in the VBA macro
Use ClearContents to remove values and formulas while preserving data validation and formatting.
In Excel, open the workbook and select Developer > Visual Basic. Open the module containing the macro.
Find the Range.Clear statement and replace Clear with ClearContents. For example, use Worksheets("Sheet1").UsedRange.ClearContents to clear the used range on Sheet1.
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.
Run the macro from Developer > Macros, then check the cleared cells to confirm that their drop-down lists and conditional formatting remain.

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. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
- 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. Clear the contents: On the Home tab, open Clear and choose Clear Contents. Menu labels may vary by version.
- 4. Check and save: Click a cleared cell to check its drop-down list, then select File > Save to save the workbook.

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.




