Speeding up loop: deleting rows

[leal72]

I have some data files that will have around 8200 rows of data but in order to reduce file size we want to delete some of the rows. Right now I have it set up so that the user can decide which rows to keep. Is there any way to speed up the loop I have that is deleting the rows?

[vba]
DtPnt = InputBox("Process every __th data point", "Set number by which to reduce data")
DtCnt = DtPnt

' Loop through selected files and add to Results workbook
For z = LBound(FileName) To UBound(FileName)

Set wkbTemp = Workbooks.Open(FileName:=FileName(z))
ActiveSheet.Move after:=Workbooks(WkbName).Sheets("SummaryData")

' grab activesheet name
WsName = ActiveSheet.Name

CntRow = ActiveSheet.Range("AA1").End(xlDown).Row

LastRow = ActiveSheet.Range("A" & CntRow).End(xlUp).Row

For x = LastRow To 10 Step -1
If DtPnt < DtCnt Then
ActiveSheet.Range("A" & x).EntireRow.Delete
DtPnt = DtPnt + 1
Else
DtPnt = 1
End If
Next x

DtPnt = 0
CntRow = 0
LastRow = 0

Next z
[/vba]

[JP2112]

Have you turned off screen updating?

[VBA]Application.ScreenUpdating = False ' at the beginning of your loop

Application.ScreenUpdating = True ' at the end of your loop[/VBA]

[leal72]

Have you turned off screen updating?

[vba]Application.ScreenUpdating = False ' at the beginning of your loop

Application.ScreenUpdating = True ' at the end of your loop[/vba]

yes I have.

I'm hoping there's maybe a function or another way to write the loop so that I will not take so long to run.

[JP2112]

There are few alternate approaches.

You could read the range into an array, and loop through the array in memory instead of hitting the worksheet N number of times.

Have you tried http://support.microsoft.com/kb/213438 ?

You could restructure the way data is written to the worksheet, so it's easier to delete.

Instead of copying the newly opened worksheet over to the existing workbook, only to delete the rows, you could process that sheet instead and only copy over the necessary rows.

If you're only copying rows in order to get a summary, after opening the workbook you could just sum or count data, instead of copying it over. A Pivot Table might also be appropriate.

[mikerickson]

Similar to creating an array of ranges, you could use Union to create a discontinous range and then delete that range at one go.

[VBA]Set myRange = Rows(1)
For rowNum = 5 to 30 step 4
Set myRange = Application.Union(Rows(rowNum),myRange)
Next rowNum

myRange.Delete [/VBA]

[mdmackillop]

You can also use AutoFilter and SpecialCells(xlCellTypeVisible) to create a range to be deleted.
[VBA]
Dim rng As Range
Set rng = Intersect(Columns("D"), ActiveSheet.UsedRange)
rng.AutoFilter Field:=1, Criteria1:="="
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter

[/VBA]

[leal72]

I'll try these out and post back. Thank you all very much!

[leal72]

this one is a little quicker than what I was using, took about 2 seconds to go through 8200 rows of data

[vba]
Application.ScreenUpdating = False

DtPnt = InputBox("Process every __th data point", "Set number by which to reduce data")

DtCnt = DtPnt
AllRow = Sheets(WsName).Range("AA1").End(xlDown).Row

LastRow = Sheets(WsName).Range("A" & AllRow).End(xlUp).Row

Range("H9").Resize((LastRow + 1) - 9).FormulaR1C1 = "=IF(MOD(RC[-7]," & DtCnt & "),0,1)"

Set RngH = Range("H10:H" & LastRow - 1)
RngH.AutoFilter Field:=1, Criteria1:="0"
RngH.SpecialCells(xlCellTypeVisible).EntireRow.Delete
[/vba]

this one took about 3 seconds for going through 8200 rows of data

[vba]
Application.ScreenUpdating = False

DtPnt = InputBox("Process every __th data point", "Set number by which to reduce data")
DtCnt = DtPnt

WsName = ActiveSheet.Name
AllRow = Sheets(WsName).Range("AA1").End(xlDown).Row

LastRow = Sheets(WsName).Range("A" & AllRow).End(xlUp).Row
For x = LastRow - 1 To 10 Step -1
If DtPnt < DtCnt Then
Sheets(WsName).Range("A" & x).EntireRow.Delete
DtPnt = DtPnt + 1
Else
DtPnt = 1
End If
Next x
[/vba]

I could not get the microsoft example to work properly unless the rows of data began on row 1, probably just need to look at it another time. Not real familiar with pivot tables so I did not try that route yet.

I example using Union took a bit longer than what I had.

Thanks again for all the help. Very much appreciate it.