That means Excel has to perform the delete rows command 2,197 times to delete all the filtered rows. ?Selection.SpecialCells(xlCellTypeVisible).Areas.Count
Tidymymusic hanging while identifying duplicate files code#
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. These non-contiguous ranges are known as areas in VBA. The unsorted data creates a series of non-contiguous ranges for the visible rows. It may be faster or slower on your computer, but this is a very simple data set with no formulas. If we filter the range for Red only, then delete the rows, the process takes about…Įxcel does enter “Not Responding” (freezing) mode for a few seconds too, which is always scary. Let's say we want to delete the rows that contain “Red” in the Color column. There are 10,000 rows of data and the Color column is NOT sorted. The first column contains a color (Red, Blue, or Green), and the second column contains a random number. 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!įor this example I created a very simple data set that has two columns. Excel will sometimes enter Not Responding mode. This process takes longer with larger data sets that contain more rows, columns, and formulas. 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. Well, deleting rows on a filtered range can be a very labor intensive process for Excel. 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…Įven worse, sometimes Excel freezes or crashes when you attempt to delete rows. Has your computer ever started smoking after you attempted to delete rows from a large filtered range? 🙂 Sort Before Deleting Rows.xlsx Deleting Filtered Rows Can Take A Long Time! Watch on YouTube & Subscribe to our Channel Download the Sample Fileĭownload the sample Excel file to follow along.
0 kommentar(er)
