Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Clear Contents Macro

  1. #1
    Join Date
    24th June 2004
    Posts
    48

    Clear Contents Macro

    Hi! Here's my problem...

    I have a protected worksheet in which certain calculations are performed based on certain inputs.

    For example, let's take Column E:

    Starting in cell E4, I have cells in that column which are input cells and further down the column are calculated cells which are locked and whose formulas are hidden.

    I was looking for a macro which would go down col E and clear contents of all unlocked cells without messing up any of the protected cells.

    Also, there are certain cells in which I want to clear contents and some cells that I want the macro to insert a "0" value.

    The input cells are non-contigous.

    Any help will be appreciated.

    Thanks,

    K

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,423

    Re: Clear Contents Macro

    Surely if your formula cells are protected then you simply need to select the column & use delete. Why a macro?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    About me.

  3. #3
    Join Date
    24th June 2004
    Posts
    48

    Re: Clear Contents Macro

    ROY:

    It's my input cells that I want to clear the contents. I do not want to delete the entire column.

    In COl E, I've a bunch of input cells starting E4, which I want a macro to clear the contents and then there are some input cells in which the macro should insert a value of "0".

    Since the calculated (protected) cells are mixed in with the input cells and the the data range goes down from e4 - e500. I think a macro would work more efficiently than a user trying to clear the input cells and insert a "0" in other input cells.

    K

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Posts
    966

    Re: Clear Contents Macro

    kfotedar,

    I recommend you turn on the macro recorder and do the actions you need to do. Excel will capture the deleting and entering of zero as you type.

    If the cells you need to clear aren't in the same place all the time, you may need to name them and select them by name before you clear them and set the values to zero.

    Jim

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd April 2006
    Posts
    243

    Re: Clear Contents Macro

    I would suggest using the 'SpecialCells(xlCellTypeConstants)' method to select only the input cells & exclude any cells containing formula.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,423

    Re: Clear Contents Macro

    How are you protecting the Sheet? The correct way would be to select all the cells by clicking the grey box at the junction of the Row & Header Columns. From the Format menu select Cells then Protection & uncheck Locked & Hidden. Then select all cells containing formulas ( use he Ctrl button to allow multiple selections), Format > Cells > Protection & this time check Locked & Hidden. Now protect the sheet. Formula cells cannot be deleted but all others can. Why make it complicated with macros?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    About me.

  7. #7
    Join Date
    24th August 2005
    Posts
    195

    Re: Clear Contents Macro

    Why not use a derivative of:

    VB:
    If ActiveCell.locked = False And Activecell.Value <> 0 Then 
        ActiveCell.Formula = "0" 
    End If 
    
    
    Rico.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th June 2004
    Posts
    48

    Re: Clear Contents Macro

    Thanks to all of you ! for your ideas and suggestions.


    K

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: April 26th, 2008, 12:43
  2. Clear Contents??
    By Sirius Black in forum Excel and/or Powerpoint Help
    Replies: 4
    Last Post: June 20th, 2006, 22:21
  3. Clear Contents Macro
    By csinger in forum EXCEL HELP
    Replies: 5
    Last Post: October 28th, 2005, 03:27
  4. Replies: 3
    Last Post: October 23rd, 2005, 09:00

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
  •  
porno