+ Reply to Thread
Results 1 to 10 of 10

Clear cells BUT NOT FORMULA of 2 ranges in a macro

  1. #1
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Clear cells BUT NOT FORMULA of 2 ranges in a macro

    Hello, I am trying to use a button to clear 2 ranges of cells within the active worksheet, but without deleting the formulas that are in those cells
    I picked the code from different sources, and tried to make it work, but I am struggling a lot.
    Help please

    Sub ClearContent()

    Dim val As Range

    Dim r1 As Range, r2 As Range
    Set r1 = ThisWorkbook.ActiveSheet.Range("V4:V19")
    Set r2 = ThisWorkbook.ActiveSheet.Range("A68:U121")

    Set val = Sheet1.r1.SpecialCells(xlCellTypeConstants)
    Set val = Sheet1.r2.SpecialCells(xlCellTypeConstants)

    On Error Resume Next
    val.ClearContents

    End Sub

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    You could simply use:

    Please Login or Register  to view this content.
    Rory

  3. #3
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    I typed it correctly, there is no error in the module, but when I run the macro or use it through my button, it doesn't clear the ranges.
    I copy pasted your code.

  4. #4
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    I tried

    Sub ClearContent()
    Range("V4:V5").ClearContents
    End Sub
    It cleared the content and the formula, as I expected. But it works.
    Now if I replace this code with

    Sub ClearContent()
    ThisWorkbook.ActiveSheet.Range("V4:V5").SpecialCells(xlCellTypeConstants).ClearContents
    End Sub
    It says:
    Run-time error '1004':
    No cells were found.

    Anyone has a clue what's going on?

    error.png

    I even tried the following code.
    No error, but it doesn't work.

    Sub ClearContent()
    On Error Resume Next
    ThisWorkbook.ActiveSheet.Range("V4:V5").SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo 0
    End Sub
    Last edited by Soraka; 03-29-2023 at 04:09 PM.

  5. #5
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    I noticed a strange behavior.
    Let's assume I have values in the cells I want to clear (but keep the formula).
    I click the button that has the code as shown in the very last quote of the previous message.

    Nothing happens.

    I then go in VBA and remove the 2 lines "On Error Resume Next" as well as "On Error GoTo 0"

    I close vba and try again by clicking the button that has the code.
    It gives an error 1004, no cells were found. But it does not clear the content, the values are still shown in the cells.

    I go back in vba and add again the 2 lines I had removed. I close vba, return to excel and
    if I double click in a cell and click elsewhere, it will put 0 in the cells I want to clear, but keep the formula.

    What is this sorcery, I am so lost.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    Which workbook is the code in? It's trying to clear cells in the activesheet of the workbook with the code in it, which may or may not be the active workbook. If the code is in your personal macro workbook for example, you probably meant to use activeworkbook rather than thisworkbook.

  7. #7
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    It was the correct workbook.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    Just to clarify: that code will only clear cells that do not have formulas in them.

  9. #9
    Registered User
    Join Date
    02-08-2023
    Location
    Germany
    MS-Off Ver
    365
    Posts
    80

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    Quote Originally Posted by rorya View Post
    Just to clarify: that code will only clear cells that do not have formulas in them.
    This is not what I want then.
    I never asked for that.

    I have a cell that has a formula and that formula leads to a module that will write a value, which is the result of logical expression like people say, in the same cell the formula that calls that module is.
    So I want to either:
    - Tell the module to write that result value in another cell without formula (so i can clear content that cell without destroying my whole thing)
    - Or, somehow manage to clear the result value in the cell that has the formula without removing the formula and without getting this stupid error "1004 no cells were found", because i think i get this error solely because the result value is written by excel, not typed manually.
    Last edited by Soraka; 03-31-2023 at 05:12 AM.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Clear cells BUT NOT FORMULA of 2 ranges in a macro

    You cannot clear a cell that has a formula while leaving the formula, it just doesn't make sense. You clear the cells that are inputs to the formula.

    Since you haven't clarified what code is meant by "that formula leads to a module that will write a value", I can't comment further.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Want a Code to Clear Defined Ranges of cells
    By rexcel548562 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2022, 03:27 PM
  2. VBA to Clear 1 Cell and Clear other cells Formula only
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2017, 12:56 PM
  3. [SOLVED] Macro to clear cells in range & move up if certain cells meet criteria
    By samder68 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2016, 10:52 PM
  4. [SOLVED] VBA help needed to search two text strings and select ranges then clear cells
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2014, 12:30 PM
  5. Clear names Cells / Ranges
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 02:32 PM
  6. macro to clear contents of two ranges if cell is blank
    By fisher-price in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2011, 10:39 PM
  7. clear data, retain formula/named ranges
    By inky in forum Excel General
    Replies: 2
    Last Post: 01-28-2008, 05:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1