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.

 

[VoG]

Try

Range(Cells(1, 4), Cells(howLong, howWide)).EntireColumn.Delete

 

[Norie]

Try this.

Columns(4).Resize(howWide-3).EntireColumn.Delete

Or this.

 

[LadiesAndGentlemen]

Unfortunately, both of those suggestions gave me the same error. I appreciate your time. Any other ideas?

 

[Norie]

What other code is there?

What values do howWide and howLong hold when the code fails?

 

[LadiesAndGentlemen]

The rest of my code is rather involved and quite long so I wouldn't even know which parts to post. I can, however, try to do a better job of explaining what my code is doing and essentially give you my code's comments:
1)I start off with a sheet of about 4,500(rows) x 210(columns).
2)I transpose that data using excel's paste special function
3)I iterate through my columns and in each iteration copy and paste the values of the column below the last entry in column 3 (ex: take the 5 values in column 3 and put them in rows 6-10 of column 3. Take the 5 values in column 4 and put them in rows 11-15 of column 3...etc)
4)Now that I have all my data in columns 1, 2, and 3, I want to delete all other cells

This is where I run into trouble.

howWide and howLong are 4,033 and 846,721 respectively. If I change this to 1000 and 2, it still doesn't work. If I change it to 26 and 1, it does work.

Hopefully that helps a bit. The program works perfectly in its entirely on sheets that start out as 100(rows)x210(columns) so I don't know why it doesn't work for bigger sets since I am pretty positive I am not hitting any of excels size limits as far as rows and columns go

 

[LadiesAndGentlemen]

I think I have found the root of the issue. Trying to manually do the operation my code was having trouble with, I realized that even without VBA, Excel has trouble deleting 850,000 rows at once. In other words, it was not a problem with the code but rather with Excel's capacity. I am currently working on revising my code to delete only a few thousand rows at a time (which will still be faster than doing it one by one).

If anyone has other suggestions that might be more effective, I would be very grateful. Otherwise, thank you so much for the help you already gave.

 

[Norie]

You aren't deleting a specific no of rows, you are deleting entire columns which ie all rows.

 

[LadiesAndGentlemen]

Right. Actually at various parts of my program I end up deleting entire rows and entire columns. The problem is that Excel's row capacity is 1,000,000 and my data takes up 9,500,000 rows which is close enough to basically incapacitate all of Excel's functions.

Thanks again.

 

[Norie]

If you have 9m+ rows of data how have you managed to get it into Excel?