Deleting or cancelling large amount of data in excel

[giorus]

Got the following problem apparently easy but it's driving me crazy.
I've a large file (c40mb) containing large matrices full of datas and
formulas (ie thousands of rows / approx 100 columns)
Whenever I try to delete the content (or clear content) of one of this
large matrices excel takes ages to do it.

I have tried (note: calculation is switched to manual):
- clear all
- clear content
- delete columns
- copy and paste special as value and then delete but when I paste it
takes ages

always the same story: minutes to do it.

PLEASE HELP. I'M STUCK
Giovanni

 

[Roger Govier]

HI

Do you have any event driven macros in the worksheet?
If so, in the immediate window of the VBE type
Application.EnableEvents = False and press Enter

Then carry out your deletions or clearing.

When you have finished go back to the VBE and type.
Application.EnableEvents = True and press Enter

 

[FennisDuck]

try hilighting 1 colum at a time and delete that it might be a bit time
consuming but it will eventually clear the sheet
or you could run a macro to clear several rows at a time

try this , but it will only delete if there are no cells protected/ hidden
the macro will delete 20 rows at a time, and loops 10 times deleting a max
of 1000 rows
(if you find this soloution works or you have to amended it please post it
to the group for all to see - thanks)

Sub DELROWS()
'
' DELROWS Macro
'
myNum = 10 ' you can change this value but not to high
counter = 0

Do While myNum > counter
myNum = myNum
counter = counter + 1
'
Rows("1:20").Select ' selects top 20 rows you can change this
value also
Selection.Delete Shift:=xlUp
Loop
End Sub