VBA code from Excel 2016 does not work in Excel 365

Question

To clear the contents of a row (but not the formulas or formats) I use the command:

rows(nrow).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

(with nrow defined as integer)

In the Excel 2016 versus it works OK but in Excel 365 I get an error message:

Error - 2147352560(80020010) during execution

Method ClearContents of object Range failed

Answer 1

Just following up. Has your issue been resolved?

Answer 2

Do you have any merged cells? If so, try using:

rows(nrow).EntireRow.SpecialCells(xlCellTypeConstants).Value = vbnullstring

Answer 3

Not sure if the following will fix your problem. However, the code will error if no constants found.

Try the following that handles no constants found.

The MsgBox if no constants found is optional.

Sub ClearContents()

Dim nrow As Integer 

Dim rngToClear As Range 

nrow = 3        'Row number assigned for testing 

On Error Resume Next    'Next line errors if no constants so error needs to be handled

Set rngToClear = Rows(nrow).EntireRow.SpecialCells(xlCellTypeConstants) 

On Error GoTo 0     'Resume Error trapping ASAP 

If Not rngToClear Is Nothing Then   'Not nothing then is something (Constants found) 

    rngToClear.ClearContents 

Else 

    MsgBox "No constants found in row " & nrow  'Optional (Used in testing) 

End If 

End Sub