3

I have a sheet in Excel 2010 which is setup as a pseudo form (I didn't create it, I'm just trying to fix it) so formatting suggests that the user can only enter in certain cells. Depending on certain functionality these areas need to be reset, i.e. cleared although formulae and standard/conditional formatting need to be kept. I have defined each of these cells/ranges as named ranges so I can easily loop through them using the following code: -

Public Sub ResetDetailSheet()
    Dim nm As Name

    With ThisWorkbook
        For Each nm In .Names
            If Left(nm.Name, 9) = "nmrDetail" Then
                Range(nm.Name).SpecialCells(xlCellTypeConstants).ClearContents
            End If
        Next
    End With
End Sub

For some reason instead of clearing the constants from the specific range it is clearing constants from the entire sheet so I am losing all titles/headings. Formulae and standard/conditional formatting are staying as expected.

What am I doing wrong?!?!

As a test using the immediate window I tried clearing a specific cell, e.g.

Range("G7").SpecialCells(xlCellTypeConstants).ClearContents

But this still cleared all constants from the entire sheet.

What am I missing? I don't understand. Maybe I'm being dumb. Sorry, I can't upload an example. This place is pretty locked down.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Darybrain
  • 63
  • 1
  • 8

1 Answers1

4

Range({any single cell}).SpecialCells({whatever}) seems to work off the entire sheet.

Range({more than one cell}).SpecialCells({whatever}) seems to work off the specified cells.

So, make sure your range has more than a single cell before you clear it - if the range is only a single cell, then check if it .HasFormula; if that's the case then its .Value isn't a constant:

With ThisWorkbook
    For Each nm In .Names
        If Left(nm.Name, 9) = "nmrDetail" Then
            If nm.RefersToRange.Count > 1 Then
                nm.RefersToRange.SpecialCells(xlCellTypeConstants).ClearContents
            ElseIf Not nm.RefersToRange.HasFormula Then
                nm.RefersToRange.ClearContents
            End If
        End If
    Next
End With

Note that I'm using Name.RefersToRange instead of fetching the range by name off the active sheet.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • thanks I'll try this tomorrow. I gave up for the night - found other silly things. There are a number of single cell references. I was not aware of this single cell multiple cell difference with SpecialCells. Do you think using a range of A1:A! would work? Using your example, how do I keep any standard/conditional formatting with the single cells? – Darybrain Nov 10 '16 at 23:41
  • Wouldn't `ClearContents` preserve formatting? Haven't tested... worst case just set its `.Value` to `vbNullString`. – Mathieu Guindon Nov 10 '16 at 23:43
  • It's too late here. My brain is mush. I'll shut up now and come back tomorrow. – Darybrain Nov 10 '16 at 23:45
  • Thanks fellas, worked like a charm. Pity MSDN didn't make it more clear that SpecialCells only works for multiple cells. I should have tested it anyway. Slight amendments to the Sub above are `On Error Resume Next` at the top as SpecialCells will fail if there are no relevant cells and there is no elegant way to test for it. Plus, when the cell count is 1 with no formula to `nm.RefersToRange.MergeArea.ClearContents` to handle any possible merged cells although this will still work for cells that are not merged. – Darybrain Nov 13 '16 at 17:06
  • 1
    This is Rubberduck inspection worthy... See also https://stackoverflow.com/a/31346246/5757159 – ThunderFrame Feb 10 '18 at 11:02
  • @MathieuGuindon You made a great conclusion. I have a bunch of tables I'm trying to clear but the routine to clear a single column, single row table was clearing other cells on the sheet. Your example is a good demonstration of how to handle the single cell scenario. – Ben Jun 12 '19 at 21:13