XP running out of memory when using macro

[JamieD]

Hi all,
I have a macro which causes XP and and 2000 to produce this error
"Excel cannot complete this task with available resources. Choose less
data or close other applications". However it will run on 98.
The macro is quite large but nothing that is amazing. Unfortunately I
have inherited the workbook off someone and it contains numorous macros
that are all run in turn from one master macro (baisically a bit of a
mess and hard to tell whats going on).
The macro is below, it is baisically inserting zeros to allow sorting
on each sheet.
"Sheets("TEAM A").Select
ActiveWindow.ScrollRow = 984
Range("A1000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "0"
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.filldown
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("DAY 1 TEAM B").Select
ActiveWindow.ScrollRow = 984
Range("A1000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "0"
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.filldown
ActiveWindow.ScrollRow = 1
Range("A1").Select"
The macro is longer than this, it is baisically the same but repeats
another 20 times (with the sheet name changing each time). I haven't
posted the whole thing as it is very long but if I have to thats no
problem.
I am not runniong any other programs when running this macro and have
tried it on many machines.
Hope someone can help.

Many Thanks

J

 

[Don Guillett]

something like this (untested) should help. No selection

for each sh in sheets 'as written will do ALL sheets
x=sh.range(cells(65536,"a").end(xlup).row
sh.range(cells(1,"a"),cells(x,"a"))=0
next sh

 

[Tom Ogilvy]

Unless there is some data below cell A999, this fills the entire column with
zeros. That can make your file huge - do you really want to do that?

Why do you think you need to do that to be able to sort?

 

[JamieD]

Hi thanks for the rplies,
I have decided to start from scratch with this workbook so the tips
will come in very usefull.
Howvever I have another question. I am using the below code to sort the
data from one sheet (by Team Name) then copy onto another. I have used
the "CurrentRegion" as the workload will vary from week to week.
However after running the macro it always errors with "the paste method
of worksheet class faild".I have tried using paste values only but
still no luck.

Sheets("DAY 1 CUTTING LIST").Select
ActiveWindow.SmallScroll ToRight:=4
Selection.AutoFilter Field:=10, Criteria1:="TEAM B"
ActiveWindow.ScrollColumn = 1
Range("A10").CurrentRegion.Copy Sheets("DAY 1 TEAM
B").Range("A10")
Range("A10").Select
ActiveSheet.Paste
Range("A10").Select
Sheets("DAY 1 CUTTING LIST").Select

Many Thanks

J