none
VBA Macro Not Working For ClearContentsNotFormulas RRS feed

  • Question

  • Hi:  I created a macro, ClearContentsNotFormulas (see below), and before using this macro, I validated that the data in the cells with formulas that I want to clear and leave the formulas is CellType = Values, but the macro does not work?

    The values are paste linked from a different Excel file and when the appear in my target Excel file, the cells they are in are formatted as "Percentage."  For example, 53% (i.e., all "Values" have the % sign).

    How do I fix this problem.  I look forward to your response and thanks for your help.

    Sub ClearDataNotFormulas()
        
        On Error Resume Next
        
        Cells.SpecialCells(xlCellTypeValues).ClearContents

    End Sub

    FrankHW

    Saturday, September 15, 2018 1:21 AM

All replies

  • You have used the incorrect parameter (argument) for the values which are actually referred to as constants.

    Cells.SpecialCells(xlCellTypeConstants).ClearContents

    See the following link for the xlCellType enumeration

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlcelltype?view=excel-pia


    Regards, OssieMac

    Sunday, September 16, 2018 12:11 AM
  • OssieMac:

    Actually, I am trying to clear cells of constants not formulas in non-contiguous ranges of cells.  I tried using the macro below and it didn't work.

    Your assistance is greatly appreciated.  Regards, FrankHW

    Sub ClearConstantsNotFormulas()
        On Error Resume Next
        Range("E29:H43,E49:H63,E69:H83").SpecialCells(xlCellTypeConstants).ClearContents
    End Sub

    Sunday, September 16, 2018 11:29 PM
  • I have tested as follows.

    Created a dummy set of formulas

    Copied -> PasteSoecial ->Values on specific non the contiguous ranges to create constants within the formula range.

    Colored the ranges containing the resulting constants for easy identification.

    Ran your example code to clear the constants.

    All works perfectly.

    My test code as follows.

    Sub CreateTestData()
        'This sub was run first to create the dummy data
       
        Cells.Clear 'Clear all cells on the worksheet including formatting.
       
        'Create a range with formulas
        Range("E20:H90").Formula = "=RANDBETWEEN(1,5)"
       
        'Copy -> PasteSpecial specific range
        With Range("E29:H43")
           .Copy
           .PasteSpecial xlPasteValues
           .Interior.Color = vbYellow
        End With
        Application.CutCopyMode = False
       
        With Range("E49:H63")
           .Copy
           .PasteSpecial xlPasteValues
           .Interior.Color = vbYellow
        End With
        Application.CutCopyMode = False
       
        With Range("E69:H83")
           .Copy
           .PasteSpecial xlPasteValues
           .Interior.Color = vbYellow
        End With
        Application.CutCopyMode = False
       
        'Replace some numerics with strings for the test
        Range("E20:H90").Replace What:="2", Replacement:="OssieMac", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        
    End Sub

    Sub ClearConstantsNotFormulas()
         'This code run subsequently test clearing the constants only.
         Range("E29:H43,E49:H63,E69:H83").SpecialCells(xlCellTypeConstants).ClearContents
        
    End Sub


    Regards, OssieMac

    Monday, September 17, 2018 1:53 AM
  • Hi Frank,

    Did OssieMac's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by FrankHW Monday, September 17, 2018 2:33 PM
    • Unmarked as answer by Yuki Lou-MSFTModerator Tuesday, September 18, 2018 5:07 AM
    Monday, September 17, 2018 6:38 AM
    Moderator
  • OssieMac:

    I can't thank you enough.  When I went online, I found lots of examples that addressed applying a Macro to Cells, but not Ranges, and these macros were remarkably brief (i.e., 2-3 lines of code, excluding Sub and End Sub).

    By the way, I had already downloaded the information about CellTypes from the website you referenced.

    As you surmised, I am a novice at Excel Macros; however, my skills with Excel beyond Macros is clearly way above Intermediate.

    I have avoided Macros because, until now, I could do everything I wanted to do using Excel functions.

    As with Excel functions, the key to success with Macros is understanding the "Syntax."  When it comes to Excel, "syntax is king."

    Thanks again and have a great day.  If you are OK with my contacting in future, I will use this forum to contact you.  Lastly, I am happy to pay for your time and effort.

    If you would like to contact me, I am Professor of Pathology at the Paul L. Foster School of Medicine, Texas Tech University Health Sciences Center and my e-mail address is:  frank.wians@ttuhsc.edu

    Monday, September 17, 2018 2:42 PM
  • OssieMac:

    I ran the Macro below from your response and gave an error message, "No Cells Found."  I know, I should have done this first before replying that the problem was solved.

    Now, I am suspicious that there is something about the content of my cells in the selected ranges that the Macro below is not recognizing as "Constants."

    Below is an example of a typical cell content formula:  

    =IF('[JWsummTblsQ1-18.xlsm]Sheet1'!S7="",#N/A,'[JWsummTblsQ1-18.xlsm]Sheet1'!S7)

    Sub ClearConstantsNotFormulas()
         'This code run subsequently test clearing the constants only.
         Range("E29:H43,E49:H63,E69:H83").SpecialCells(xlCellTypeConstants).ClearContents
    End Sub

    Monday, September 17, 2018 3:06 PM
  • Hello Frank,

    As a matter of interest to me, did you try opening a new workbook and inserting both your code and my code example to create some dummy data with a mixture of formulas and constants and then run your code to see if it clears the constants?


    Regards, OssieMac

    Monday, September 17, 2018 9:06 PM