Quicker way to delete rows

[Brian.Crawford]

I import data from one WB into another but only need about 20-25% of the imported rows (currenlty about 1,500 growing to 5,000 to 6,000 by year-end. I currently do a
For i= CalculateTotalRows to 1 step -1
" if not ctiteria the delete entire row"
The import is quick (few seconds) but deleting rows (that don't match one specific criteria - ie one column matching a variable) is slow, the process currently doing about 4-5 deletions per second.
Is there a way to speed this process up dramatically.
The rows are sorted by my criteria so one thought was to find the row from the beginning to where my data starts and then deleting those all at once and then doing to same for those after my data (or visa-versa). At least then it's not one record at a time.
When I process the entire sheet but with no deletions it is very fast, 1500 taking about 10-15 seconds

Any thoughts
thanks all
Brian

 

[Peter_SSs]

Brian

Without the details of your sheet and code it's impossible to give exact code but here's a few things that should help.

1. Turn ScreenUpdating off while your code is running.

2. If your sheet contains many formulas, turn automatic calculation off while the code runs.

3. Don't delete row-by-row. Your idea of deleting groups of rows is definitely on the right track, given that the data you want is in a contiguous block as I understand it. I would try this ..

a) Use AutoFilter to filter your particular column for NOT EQUAL TO your wanted criteria.

b) Delete all the visible rows below the headings (I'm assuming headings).

c) Remove the Autofilter.


If you need specific help with the code then post back with more details about the column in question, the criteria you want to keep in that column, what Excel version you are using etc.

 

[Brian.Crawford]

Assuming Application.Screenupdating & Application.calculation have scope beyond the (parent) SUB they are set in, both were off when this lower level sub was called.

My sheet does not have headers, it is raw data used only to feed SUMIFs on another sheet. I don't think I can filter without headers - can I ?

If I can, is it simply something like
Sht.Range("A:A").visible.entirerow.delete

that would get it down to one big delete !
Thanks

 

[Peter_SSs]

Brian

If you are not absolutely sure what the status of screenupdating and calculation are when you enter a procedure but want them a particular way during the procedure, you can use a structure like I have at the start and end of the code below. That way you get these how you want without upsetting anything else.

You do need a heading to do AutoFilter so I have made a suggestion of how to deal with that and the filtering. Hope it is useful to you.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Unwanted_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> bUpdating <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>                <SPAN style="color:#007F00">'***</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lCalc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>                       <SPAN style="color:#007F00">'***</SPAN><br>    <br>    <SPAN style="color:#007F00">'Capture current status of screenupdating & calculation</SPAN><br>    <SPAN style="color:#007F00">'then set them how you want for this procedure</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        bUpdating = .ScreenUpdating<br>        lCalc = .Calculation                <SPAN style="color:#007F00">'***</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        .Calculation = xlCalculationManual<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Put a new row 1 in to hold a temp header</SPAN><br>    Rows(1).Insert<br>    <br>    <SPAN style="color:#007F00">'With your column of interest</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("J1", Range("J" & Rows.Count).End(xlUp))<br>        <br>        <SPAN style="color:#007F00">'temp header</SPAN><br>        .Cells(1, 1).Value = "temp"<br>        <br>        <SPAN style="color:#007F00">'filter for what you don't want. eg if you want</SPAN><br>        <SPAN style="color:#007F00">'to keep xxx values, filter for <>xxx</SPAN><br>        .AutoFilter Field:=1, Criteria1:="<>xxx"<br>        <br>        <SPAN style="color:#007F00">'delete these unwanted rows, including temp header row</SPAN><br>        <SPAN style="color:#007F00">'this will also remove the autofilter</SPAN><br>        <SPAN style="color:#007F00">'leaving the rows you want to keep visible again</SPAN><br>        .EntireRow.Delete<br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Set screenupdating & calculation back to whatever</SPAN><br>    <SPAN style="color:#007F00">'they were when this procedure was called</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .ScreenUpdating = bUpdating<br>        .Calculation = lCalc                <SPAN style="color:#007F00">'***</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Brian.Crawford]

Thanks, that looks to be exactly what I need.
I have this issue in a number of places over 12 WBs (in 2-3 places per WB) in the project, so this will be a big performance boost.
What I now do in a 2-3 minutes (even before trying above) , used to take the user about 10days/month of data entry/consolidation so they are pretty happy so far, but this will likley get it down to well under a minute. The Budget Analyst will now be able to do her real job!

Thanks again
Brian

 

[Brian.Crawford]

This is basically instantaneous !

 

[Peter_SSs]

This is basically instantaneous !

That sounds like a desirable outcome!
Thanks for the feedback.

Edit: Brian, I have just looked at my code again and I couldn't have had my brain quite in the right gear when I posted that code. I had the wrong type of variable for Application.Calculation. I have edited my previous code to correct that so please look at the code again and look at all the lines marked with '***

 

[Brian.Crawford]

I did notice an oddity in that (resulting value of "bCalc") when I ran it but was so happy at the result I ignored it for now.
Will make the change.
I changed the code a tiny bit to make it more generic for my various situations. I can post it back if you'd like.
Brian

 

[Peter_SSs]

I changed the code a tiny bit to make it more generic for my various situations. I can post it back if you'd like.
Brian

Brian

Unless you need further help with tweaking your code I don't think it necessary to post it.

 

[Brian.Crawford]

No, it's good for now.
I'm about to post a query (related to a previous post as per the URL below that still isn't resolved) if you'd care to add you expertise to the the upcoming one.
How to copy Named ranges when recreating (piece by piece - modules, forms, sheets) a defective WB

thanks again



http://www.mrexcel.com/forum/showthread.php?t=543065