Announcement

Collapse
No announcement yet.

Clear Contents Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    New users should read the Forum Rules before posting

    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

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          Re: Clear Contents Macro

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

          Comment


          • #6
            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

            New users should read the Forum Rules before posting

            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

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: Clear Contents Macro

              Why not use a derivative of:

              Code:
              If ActiveCell.locked = False and Activecell.Value <> 0 Then
                  ActiveCell.Formula = "0"
              end if
              Rico.

              Comment


              • #8
                Re: Clear Contents Macro

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


                K

                Comment

                Working...
                X