+ Reply to Thread
Results 1 to 5 of 5

Data Validation: How to clear/delete the content of the cell and not Data Validation List?

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    Prague, Czech Republic
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    72

    Data Validation: How to clear/delete the content of the cell and not Data Validation List?

    Hi all,

    I would really appreciate your help with one issue I've been dealing with.

    The basic description of the problem is:

    - I've got a column containing several cells that offer to choose relevant currency from the list (done by Data Validation list)
    - I need to write a macro that would hide this column if required
    & cleared all of the cells (both those, when user already selected currency from the list, as well as those still blank).


    - If I hide the column and use ClearContents function to clear the particular cells, I get the blank cells. However, the problem is that Data Validation list isn't present any longer when I uncover the column again.

    Would any of you had an idea what might be triggering this problem or what other function might be more applicable (some function mimicing the classical "delete" button on the keyboard).

    Thank you very much for any help!!!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Data Validation: How to clear/delete the content of the cell and not Data Validation L

    Hi,

    Use

    .Clear
    instead of
    .ClearContents
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-05-2014
    Location
    Prague, Czech Republic
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    72

    Re: Data Validation: How to clear/delete the content of the cell and not Data Validation L

    Thank you very much for the response! However, I've tried it already:

    Clear... deletes not only the cell content but all of the formatting so that you are left with completely white and blank cell
    Delete... deletes the entire cell so that the rows are shifted to the left
    ClearContents... deletes only the content of the cell (keeps formatting), but the data validation list reference is lost (it changes the reference to the statement "=Sheet1!#Reference!")

    Thank you in advance for reconsideration!
    Last edited by lukelucky; 01-07-2015 at 08:59 AM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Data Validation: How to clear/delete the content of the cell and not Data Validation L

    Hi,

    I read your OP as wanting to clear all cells.

    Maybe you need to consider using the Unhide column macro to reinstate any formatting and/or validations. But why if you're hiding a column do you want to do anything at all with it? Unless of course you don't want to retain the original data.

  5. #5
    Registered User
    Join Date
    02-05-2014
    Location
    Prague, Czech Republic
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    72

    Re: Data Validation: How to clear/delete the content of the cell and not Data Validation L

    Hi,

    well, it's supposed to be a part of the Excel spreadsheet that might or might not be displayed. It's based on the user's preferences if he wants to use this feature (dealing with foreing currencies exposure) and fill in some data in it or not (the choice is made by clicking on a buttons hide/show).

    So, if he clicks on the show button and then hide it again, I want to make sure that there is nothing left in the hidden area (might possibly confuse other person reviewing this document).

    But, recently I've done some changes (supposedly irrelevant to this issue) and it's working as I intended. I'm pretty new to the VBA programming, so that I've got a lot of such a funny moments...

    But, thank you very much for your interest and for trying to help me!

+ 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. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  2. Replies: 21
    Last Post: 03-14-2013, 07:33 AM
  3. [SOLVED] Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation
    By n_lindsey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2013, 02:22 AM
  4. Replies: 2
    Last Post: 11-24-2010, 01:07 PM
  5. [SOLVED] question data validation and delete/clear behavior
    By Jeff Higgins in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2005, 05:06 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