Excel Error Codes

Fix Excel 365 VBA SpecialCells ClearContents Error 2147352560

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to resolve an Excel 365 VBA runtime error triggered by clearing contents of specific cell types.

Product: Excel 365
Device & OS: not provided
Scenario: Running a VBA macro to clear the contents of constant cells in a row using SpecialCells(xlCellTypeConstants).ClearContents.
Observed behavior: The VBA statement works in Excel 2016 but throws runtime error 2147352560 (80020010) in Excel 365 when executed.

Before you start

Before modifying your VBA script, ensure you have unprotected your worksheet and check if the target rows contain any merged cells, as they are a common cause of this error.

Solution 1Recommended

Handle Merged Cells by Assigning vbNullString

Use this method if your worksheet contains merged cells, which often cause the ClearContents method to fail with an automation error.

In Excel 365, attempting to call .ClearContents on a range that intersects with merged cells can trigger error 2147352560. Assigning a null string value bypasses the strict structural checks of ClearContents while achieving the same result.

Open the VBA Editor

Press ALT + F11 to open the Microsoft Visual Basic for Applications editor and locate the module containing your macro.

Locate the problematic line

Find the line of code causing the error: Rows(nrow).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents.

Replace ClearContents

Change the end of the statement from .ClearContents to .Value = vbNullString.

Test the macro

Save your code and run the macro again to verify that the error no longer appears when encountering merged cells.

Compatibility Check: Setting the value to vbNullString is backward compatible and will continue to work perfectly in Excel 2016 and older versions.

Solution 2

Implement Error Handling for Missing Constant Cells

Apply this solution to prevent the macro from crashing when the target row contains no constant cells for SpecialCells to select.

Free Microsoft Office alternative

Try WPS Office for a Stable and Highly Compatible Macro Experience

If version-specific VBA bugs in Microsoft Excel disrupt your workflow, consider switching to WPS Office. WPS Spreadsheet offers exceptional compatibility with Excel macros and provides a stable, lightweight environment for automating your tasks without the hefty subscription fees.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open Your Macro File: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb workbook.
3. Access the Developer Tab: Navigate to the Developer tab to access the Macro Editor and run your VBA scripts smoothly.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xls) and VBA macros.Lightweight installation that runs smoothly on older and newer systems.Familiar user interface enabling a seamless migration from Microsoft Office.Completely free to download with built-in advanced data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SpecialCells(xlCellTypeConstants) fail in my macro?

This method fails and throws a runtime error if no cells within the specified range contain constants (e.g., if all cells are blank or contain only formulas). Using error handling bypasses this issue when no matching cells are found.

What does VBA runtime error 2147352560 (80020010) mean?

This is an automation error that typically occurs in Excel VBA when attempting an invalid operation on a specific range object. In the context of ClearContents, it frequently happens when the target range contains merged cells.

Is setting .Value = vbNullString better than .ClearContents?

In many automated macros, setting .Value = vbNullString is safer because it blanks out the cell content without triggering the strict range validation checks that .ClearContents performs, particularly preventing crashes when dealing with merged cells.

Microsoft Office Error Codes