VBA : clearing a sheet

  • Hello all,
    1st time post - long time reader

    My question:
    when i put in the code
    Worksheets("Name of Sheet").Range("m5:m500").ClearContents
    It takes forever to run.
    Is there a simpler/faster way to code what i want? Ive tried .clear and .delete with no progress.

    Thanx for the replies
    BTW - this happens in excel 97 and above

  • I'm no VBA expert but one way to speed up some code is to use

    Application.ScreenUpdating  =False
    'your code here
    Application.ScreenUpdating = True


    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Welcome to the board. I agree with Iridium. But, even without that it should handle it in 1-2 sec. I originally had a macro similar to that, running XL97 on a 600 MHz laptop, and it never took more than 2 sec.

    Is that the only line in the macro? Or do you have other stuff going on?

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    (retired Excel 2003 user, 3.28.2008)

  • Thanks for the replies. I actually had that part in the code but it still was really slow. I'm not sure if it's just excel trying to clear the format in each cell individually. The weird thing is that after i run the macro once it works fine.

    The first part of the code is listed below.
    When i first run the code - after about 4 minutes of stalling i break the code and it usually is stopped at the valuation sheet.

    Should i name the ranges before clearing them?

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.CutCopyMode = False
    Worksheets("Output Printable").Range("b5:s500").ClearContents
    Worksheets("Output Printable").Range("b5:s500").ClearFormats
    Application.CutCopyMode = True
    Application.Calculation = xlCalculationAutomatic

  • Quote from "big02"

    Should i name the ranges before clearing them?

    Dunno - have you tried it?

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Why the lines re CutCopyMode ? I'm not sure that the inclusion of this will speed up the code so you may as well lose them...

    and I notice that you don't turn your screenupdating back on...

    try adding that & see

    another thing you may want to try is insert a msgbox between each step... at least then you may get an idea which of the steps seems to be causing you problems

  • I am trying to do pretty much the same thing......but I want to clear the contents of everything in each worksheet.....Ive been trying this route...
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
    Next ws

    It only clears one worksheet......If this is your aim as well, to clear all worksheets then this may help, but I still need to iron out my bug...it only clears one worksheet...Maybe Im onto the wrong track?!!!?

  • hi DaniB

    i think you forgot to include one line in your code causing it to clear the contents of one sheet only

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
    ws.select 'include this line
    Next ws



    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • AHAHA !!!!!!! The simple things!! Anyway then that code should help you clear contents FOR ALL Worksheets in a work book!!!!

  • Hi x and dani,

    Generally we don't need to Select or Activate objects o work with them, and our code will be quicker if don't. Like this:

    Sub Test()
        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Sheets
        Next ws
    End Sub


  • Thanks WillR -
    i tried your suggestions and i still could not speed it up.

    unfortunatly i am running a 450 processor so that is probably somewhat the cause.

    I can narrow the range down to 250 rows so that should at least speed it up by 2.

    Thanks everyone for your input. I appreciate the help on this board.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!