How many rows can Excel handle to delete?

[amartinez988]

I'm filtering Data using VBA (of course) and I want to delete the visible Rows but they are more than 20K rows. Excel crashes trying to process the request and my question is:

What's the limit of rows that Excel can delete at one time?
Why does this happen?
Is there any workaround that is not deleting the rows on chunks?

Thank you all.

[Richard Buttrey]

Do the rows you're deleting contain formulae and if so how complex are they and how many of the columns contain formulae.

Have you tried putting Excel into manual calculation mode?

As an experiment try range valuing all the data to remove the formulae and then perform the filter and deletion. Does that speed things up?

[amartinez988]

I have no Formulas, just regular Data. It actually finished deleting the rows after a couple of mins (5-10 mins). However, If I want to delete more than that I will take like half of an hour....

I was wondering if there's a workaround or a faster way to it. I considered setting calculation to Manual.

Thanks,

[NoSparks]

I think xladept's approach in the discussion here will be of interest to you.

[Richard Buttrey]

Any chance you could upload the workbook, anonymised if necessary.

When you say you're using VBA to filter the rows do you mean that you're using a loop to process each row in turn by any chance? If so that would be exceedingly slow and I wouldn't be surprised.

[amartinez988]

When you say you're using VBA to filter the rows do you mean that you're using a loop to process each row in turn by any chance? If so that would be exceedingly slow and I wouldn't be surprised.

Nope I'm literally Filtering the Data. Here's the code:

Please Login or Register  to view this content.

[Richard Buttrey]

Try it without the Shift:=xlup which as far as I can see is redundant if you're deleting the row.

[amartinez988]

Yeap, I already tried that. I left it because it makes no difference from a performance perspective and gives more readability to the code. I always like to specify the Default property like in Range("A1").Value. Value is not really necessary but it provides meaning to the code.

However, that option to delete thousands of rows is very slow

[nilem]

rData.AutoFilter 5, "Bikes"

Try first to sort your table for the 5th column, and then run your code

[amartinez988]

Try first to sort your table for the 5th column, and then run your code

I LIKE THAT IDEA!!!!

Any suggestion on how to sort everything back to the previous state?

Thanks,

[Richard Buttrey]

I LIKE THAT IDEA!!!!

Any suggestion on how to sort everything back to the previous state?

Thanks,

Before you sort it introduce a helper column that is numbered 1,2,3....20000+

After you've sorted it, sort again by the helper column.
Has the sorting speeded things up?

If not would you upload the workbook - anonymised as necessary so we can take a look.

[amartinez988]

Before you sort it introduce a helper column that is numbered 1,2,3....20000+

After you've sorted it sort again by the helper column.
Has the sorting speeded things up?

If not would you upload the workbook - anonymised as necessary so we can take a look.

Perfect, I like the solution.

I will let you guys know if I see any improvements.

Thank you

[amartinez988]

Problem solve!!!!

Thank you both for taking the time. Here's the result:

Deleting 25K ROWS
When deleting in sort order: 1S
When deleting without sort: 10-15 mins

Winner: Sort, then delete.

Thank you both again.

[Norie]

Did you try setting calculation to manual and/or disabling events?