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

LadiesAndGentlemen

New Member
Joined
Jul 5, 2012
Messages
14
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Columns(4).Resize(howWide-3).EntireColumn.Delete
Or this.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What other code is there?

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

LadiesAndGentlemen

New Member
Joined
Jul 5, 2012
Messages
14
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

New Member
Joined
Jul 5, 2012
Messages
14

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You aren't deleting a specific no of rows, you are deleting entire columns which ie all rows.
 

LadiesAndGentlemen

New Member
Joined
Jul 5, 2012
Messages
14
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
If you have 9m+ rows of data how have you managed to get it into Excel?:confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top