VBA : clearing a sheet

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

HTH

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?

Quote

Originally posted by big02
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

Display More

Quote from "big02"

Should i name the ranges before clearing them?

Dunno - have you tried it?

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
Cells.Select
Selection.ClearContents
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
Cells.Select
Selection.ClearContents
Next ws

HTH

cheers
xlite:wink1:

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

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
        ws.Cells.ClearContents
    Next ws


End Sub

HTH