Clear Cells

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Clear Cells

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

    Default

     
    I use the following code to clear the contents of cells that are not locked on a sheet so it can be reused easily:

    Dim cl As Range
    For Each cl In ActiveSheet.UsedRange
    If Not cl.Locked Then cl.ClearContents
    Next cl

    However, it seems to take longer to run that it should. My data field is only about 25 columns by 125 rows. Is there a way to get it to go faster? Thanks.

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just timed it, and it takes about 40 seconds. And you know how people can complain. Thanks.

  3. #3
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It would go much faster if you limited the FOR/EACH loop so it didn't include all the formulas and blank cells in the UsedRange.

    For Each cell In Cells.SpecialCells(xlConstants)
    If Not cell.Locked Then cell.ClearContents
    Next cell

    You could take it a step further and add the extra constraint on the "SpecialCells(xlConstants, xlNumbers)" if you know you can further limit the scope by only looking for values.



  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Down to 5 seconds. Thanks a lot!

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Uh oh, we got problems. Get an error that it could not be run on a protected sheet. Any way around that? Thanks.

  6. #6
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I almost forgot...

    You might also try turning off screenupdating and set calculation to manual.

    So... I guess the new code might look like:

    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    For Each cell In Cells.SpecialCells(xlConstants)
    If Not cell.Locked Then cell.ClearContents
    Next cell
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True



  7. #7
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You should get in the habit of protecting the worksheets in code at workbook startup and use the "UserInterfaceOnly" option to allow your macros to run without sheet protection interference.

    If you need an example, I've got one.

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    An example would be great. You can email it to drcoon@hotmail_takethisout_.com. I.e., it is @hotmail.com. Just don't want my address to be picked up by some bot and added to a spam list. Thanks so much for your help.

  9. #9
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, guys, I've been looking for a macro like this myself. Just a thought; this could be dangerous. How 'bout starting the macro with "ActiveWorkbook.Save" and, in the macro button, include the text "If problem occurs, close without saving and re-open."
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  10. #10
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    David,

    You and anyone else who may be interested can get the example file straight from my website.

    Look for the "user_interface_only.zip" file in the VBA section.

    Regards,
    Aaron

    http://www.XL-Logic.com


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
  •  

 

 
DMCA.com