Delete Method of Range Class Failed, Run-time Error 1004

[LadiesAndGentlemen]

Hi everyone,

This is my first post so I hope someone can help me out. The VBA macro that I have written is essentially a glorified transpose function (the specifics shouldn't be important for my question). Midway through the code I need to delete a large range of cells so that a) the formatting is cleaner, b) the size of my sheet is smaller, and c) so that the "ActiveSheet.UsedRange.Columns.Count" will give me an accurate answer for the number of cells in use (and won't count the ones that I've gotten rid of).

My code works perfectly on smaller sets of about 200,000 total cells but give me a "Delete Method of Range Class Failed" error as well as an "Excel cannot complete the task with available resources" error when I try running it on larger sets of about 2,000,000 total cells. I am using Excel 2007 on Windows XP.

The code I currently have is:

Range(Cells(1, 4), Cells(howLong, howWide)).Delete
where "howLong" and "howWide" are doubles that just give the coordinate of the bottom right-most entry. "howLong" is 846,721 and "howWide" is 4033 but it should be noted that it still doesn't work when these numbers are like 2 and 1000.

Essentially, all I want to do is delete all the columns from 4 to the end of my document. This code also gives me the same error on large sample sets

Range(Cells(1, 4), Cells(1, howWide)).Select
Selection.EntireColumn.Delete

This should be so simple! I don't know what's going on.

Any help would be greatly appreciated. Many thanks in advanced.

 

[LadiesAndGentlemen]

I meant 900,000

 

[VoG]

Try deleting one column at a time. Something like

For i = howwide To 4 Step -1
    Columns(i).Delete
Next i