Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Clearing multiple cells

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to have a created button on my spreadsheet that when pressed will clear multiple cells of its data...How can this be done.

    Any info is greatly appreciated!!!
    Thanks SeanM

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First you need to define the cells to be cleared. If it is a static range of cells that will be cleared each time then naming the range is the starting point.(Insert/Name/Define)

    Then, if you use the macro recorder, following these steps once your recorder is running:

    Edit/Go To/ type name of range/OK
    Then Edit/Clear/Contents

    End macro.

    As for assigning the macro to a button:

    View/Toolbars/Forms. Click on button, drag onto spreadsheet. Text box will ask you to assign a macro - select teh one you just recorded.

    I think thats all the steps


    Nobby



  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Write the macro:

    Code:
    sub clr
    [a1:b25].clearcontents 'a1 through b25 go bye-bye
    end sub
    Click view->toolbars->forms

    Click the button, draw it, you should be prompted to assign a macro, highlight clr and assign it.

  4. #4
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you don't need to specify a specific range of cells each time, just record a macro of you clearing what you need to clear, name it, then insert a button into your sheet that executes the macro. Otherwise, you will need either a userform, or have Excel pull your clear range from the sheet itself and assign the range values to variables in your macro.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To clear all cells use:
    cells.select
    selection.clear 'clears formula, data,etc
    or try
    selection.clearcontents 'which will clear data and not formulas and formatting.

    For a range try:

    Range("A1:Z50").select
    selection.clear

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    waderw,

    you don't need select selection there,

    Range("A1:Z50").clearcontents

    does the trick.

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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