Prevent Excel from Freezing or Taking A Long Time when Deleting Rows
12

How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows

Bottom line: This quick tip will save you a lot of time when deleting rows from a filtered range or table in Excel.  Learn how to prevent Excel from freezing or crashing when deleting rows.

Skill level: Beginner

Why Deleting Rows Take A Long Time in Excel

Download the Sample File

Download the sample Excel file to follow along.

Sort Before Deleting Rows.xlsx (504.4 KB)

Deleting Filtered Rows Can Take A Long Time!

Has your computer ever started smoking after you attempted to delete rows from a large filtered range?  🙂

Or maybe you have to sit around and watch the wait cursor spin, not sure if Excel is going to come out of “Not Responding” mode, while the rows are deleted…

Even worse, sometimes Excel freezes or crashes when you attempt to delete rows.

Why does this happen?

Well, deleting rows on a filtered range can be a very labor intensive process for Excel.  If the data is not sorted then Excel has to go through each set of visible rows and delete the row sections one by one.

Deleting Unsorted Rows Can Take A Long Time in Excel

This process takes longer with larger data sets that contain more rows, columns, and formulas.  Excel will sometimes enter Not Responding mode.  We refer to this as “freezing”, but it actually causes the computer to heat up and work harder…

Why Does Unsorted Data Perform Slow?  It’s all about the Areas!

For this example I created a very simple data set that has two columns.  The first column contains a color (Red, Blue, or Green), and the second column contains a random number.

There are 10,000 rows of data and the Color column is NOT sorted.

Sample Data 10000 Rows of Unsorted Data for Delete Rows

Let’s say we want to delete the rows that contain “Red” in the Color column.

If we filter the range for Red only, then delete the rows, the process takes about…

8-10 seconds!

Excel does enter “Not Responding” (freezing) mode for a few seconds too, which is always scary.  It may be faster or slower on your computer, but this is a very simple data set with no formulas.

Each Group of Contiguous Visible Cells in a Area in Excel VBA

The unsorted data creates a series of non-contiguous ranges for the visible rows.  These non-contiguous ranges are known as areas in VBA.  We can determine how many areas we have in the filtered range by selecting all the cells in the first column of the data set, then running the following line of code in the Immediate Window of the VB Editor.

?Selection.SpecialCells(xlCellTypeVisible).Areas.Count

The result is 2,197. That means Excel has to perform the delete rows command 2,197 times to delete all the filtered rows. If there are formulas that are dependent on that range then it will have to update the range references as well.  Imagine how long it would take if we had to do that manually… 🙂

Checkout my article on the 5 Ways to use the VBA Immediate Window for tips on how to work with this great tool.

Sort to the Rescue!

Now if we sort the data first by the color column, then filter for Red only, then delete, the process takes…

less than 1 second!

I timed it with a macro and it took about 0.012 seconds to delete the sorted rows.

Sort Column Before Filtering to Delete Rows

Unless you need an excuse for a coffee break, it’s best to sort the column before deleting the rows. We went from 10 seconds to less than 1 second with a simple sort!  Woohoo!  Don’t forget to alert the media (aka boss) that you saved a bunch of time!

The sort puts the visible rows to be deleted in one contiguous range (area).  Excel is able to delete the range all at one time, instead of looping through all of the areas in the unsorted range.

Sorted Rows are in one Contiguous Range or Area and Faster to Delete

Checkout my articles on 7 keyboard shortcuts for the filter drop-down menus and 5 keyboard shortcuts for working with rows and columns to save some time with these sorting, filtering, and deleting tasks.

What If I Don’t Want to Change the Sort Order?

If the current sort order of the data is important and you don’t want to lose it, then we can add an index column to the data table.

An index column is just a column that contains the row number for each row.  This is a hard-coded value.

We can type a 1 in the first row of the data table, then fill the sequence down.

Add an Index Column Before Sorting Filtering and Deleting Rows to Retain Sort Order

Now with the index column in place, we can sort, filter, and delete the rows.  After the deletion, sort the index column in ascending order to get the original sort order back.

Sorting Prevents Computer Warming

It probably won’t solve global warming, but sorting your data can help prevent your computer from heating up (freezing). 🙂

What other performance tips do you have for sorting data?  Please leave a comment below with your suggestions, or any questions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 12 comments
Aoladari - October 12, 2017

A colleague of mine had an unusual thing happen on her spreadsheet and I can’t figure out why it happened. She sorted the data, and several rows of data disappeared! I can’t seem to figure out why it happened and I can’t find anything via Google, so I thought you might be able to help?

Reply
Bhawna - August 21, 2017

Hello Jon,
My excel file is 249 mb and has 300,000 rows of data. The quality of data is not great. When I apply filter for blank cells in one of my columns, it shows about 700,000 cells as blank and part of selection and am not able to delete these rows in one go or by breaking them into three parts. Excel goes non-responsive. My processor is intel core i3 with 8 gb RAM, so would a processor upgrade help? Also, my excel is 64 bit.

Reply
Arturo - October 5, 2016

Hi, Jon.

I have a huge data table (20K+ rows / 50 columns) with “tons” of formulas, and even if I sort the information, when I try to delete a number of rows, Excel sends a message about the lack of resources to perform the action. Any suggestion to solve it, please?

Best regards.

Reply
    Jon Acampora - October 11, 2016

    Hi Arturo,
    It’s difficult to tell, but the issue is likely due to the number of formulas in the sheet. Here is an article that has some suggestions on what might be causing the error and how to fix it.

    https://support.microsoft.com/en-us/kb/2779852

    You might want to try reducing the number of formulas if possible, or converting the formulas to values, deleting, then restoring the formulas. You could do that with a macro. I hope that helps.

    Reply
    Courtney - April 26, 2017

    Install the 64bit version of Excel. I ran into this regularly until I did so. The 32bit version is limited to something like 2GB, and a large table takes up most of that once loaded. Search the web for the actual limit and compare that to what you see Excel.exe using in the task manager. If they are close this is almost certainly the issue.

    Reply
Kevin Lehrbass - June 6, 2016

Hi Jon,

I didn’t know about the ‘area’ and using the immediate window to count them! Thanks for that!! …I also use the counter method to sort back into order after deleting the rows.

Cheers,
Kevin Lehrbass

Reply
Sadanand - June 1, 2016

Jon Sir
I am on doing a small data entry job (post retirement)and It is interesting to read
Many Many thanks for doing this valuable help to students of all walks of life

Reply
Rosemary - May 25, 2016

Thanks for this tip. I had already figured out sorting on colour but had not thought of the index column which is a great help. Cheers

Reply
Marco - May 25, 2016

Timely post. I run a macro that pastes data at the end of a table. I paste it with headers but note the row number where I start the paste. I then run an action to delete that specific row number. The table is not sorted. It does have filters on but there isn’t a specie filter criteria when I execute this macro. This still takes a long time. Could simply having the filter turned on slow down a delete function? This table has aprox 40k rows and 35 columns

Reply
indzara - May 25, 2016

Very helpful, Jon. I didn’t know this. Thanks for sharing.

Reply

Leave a Reply: