Deleting and resizing in a large data set?

[IamSierraCharlie]

Hoping someone can offer up some feedback on this one.

I have a large data set of 100000 lines where I am auto-filtering based on predefined criteria. From here, I am copying the data matching the criteria to another workbook; and then deleting the data from from the original workbook as well.

FYI, I have no real experience with VB, everything I've learned is purely from the net so please forgive my errors syntax, etc.
Here is my code:

Sub TestScript()
Sheets("Successful").Select
Dim bRow As Long, rVis As Range, rData As Range, rCount As Long

With Sheets("Successful")
.Range("A1").AutoFilter
Rows("1:1").Select
ActiveWorkbook.Sheets("Successful").AutoFilter.Sort.SortFields.Clear
bRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A1:A" & bRow).AutoFilter Field:=9, Criteria1:=Array("=#N/A", "=0"), Operator:=xlFilterValues
Set rData = Worksheets("Successful").UsedRange
Set rVis = rData.SpecialCells(xlCellTypeVisible)
rCount = WorksheetFunction.Count(rVis.Cells.SpecialCells(xlCellTypeVisible))
rVis.Copy Destination:=Worksheets("Risk-NoStockState").Range("A" & Rows.Count).End(xlUp).Offset(0)
Application.DisplayAlerts = False
If rCount <> 0 Then ' ensures the code below will only activate if there is a row to delete!
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
End If
Application.DisplayAlerts = True
.AutoFilterMode = False
End With
End Sub

The code works fine, but on large data sets, the code line "ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete" takes for ever to complete and I don't quite understand why.
I've tried various things such as setting the calculations to manual (Application.Calculation = xlCalculationManual), but it doesn't seem to make a difference.
Can someone tell me if there is a more efficient way to rewrite this line or my code to make it more efficient on larger data sets?
Any useful feedback is greatly appreciated!!
Thanks
Steve

[AlphaFrog]

Your code isn't that bad for a self-proclaimed noob. Well done.

Deleting many non-contiguous rows can take a lot of time. This is a known issue. One method to make it faster is to sort your filtered data so they are one contiguous block of rows and then delete them.

After you autofilter the data:
1.) In an empty column, put a value (any value) for the filtered rows you want to delete.
2.) Sort on that column (sorting is fast). That will put all the rows you want to delete in one contiguous block.
3.) Delete the rows.

Example: uses column Z as the temporary helper column to sort on.

Please Login or Register  to view this content.

[IamSierraCharlie]

Hi, and thanks for your prompt response.
I tested your code above over night on 100,000 lines of data.
I found a significant reduction in the script running time.
It went from around 1.5 hours to about 20 minutes (I have many subs that run similarly and I used this in all of them)
Thank-you so much for your feedback as its really helped me to fix this annoying issue - this is nothing short of spectacular!!
My original data set was 500,000 lines which I admit was a little adventurous, but 20 minutes for 100,000 is fantastic for what I'm trying to achieve - its way better than I was ever hoping for.
Regards
Steve

[AlphaFrog]

You're welcome. Glad it worked.