Deleting or cancelling large amount of data in excel

  • Thread starter Thread starter giorus
  • Start date Start date
G

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
 
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
 
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
 
Back
Top