View Full Version : Speeding up loop: deleting rows
leal72
11-02-2009, 09:33 AM
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?
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
JP2112
11-02-2009, 02:05 PM
Have you turned off screen updating?
Application.ScreenUpdating = False ' at the beginning of your loop
Application.ScreenUpdating = True ' at the end of your loop
leal72
11-02-2009, 02:11 PM
Have you turned off screen updating?
Application.ScreenUpdating = False ' at the beginning of your loop
Application.ScreenUpdating = True ' at the end of your loop
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
11-02-2009, 02:36 PM
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
11-02-2009, 09:05 PM
Similar to creating an array of ranges, you could use Union to create a discontinous range and then delete that range at one go.
Set myRange = Rows(1)
For rowNum = 5 to 30 step 4
Set myRange = Application.Union(Rows(rowNum),myRange)
Next rowNum
myRange.Delete
mdmackillop
11-03-2009, 03:10 AM
You can also use AutoFilter and SpecialCells(xlCellTypeVisible) to create a range to be deleted.
Dim rng As Range
Set rng = Intersect(Columns("D:D"), ActiveSheet.UsedRange)
rng.AutoFilter Field:=1, Criteria1:="="
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter
leal72
11-03-2009, 06:41 AM
I'll try these out and post back. Thank you all very much!
leal72
11-03-2009, 09:25 AM
this one is a little quicker than what I was using, took about 2 seconds to go through 8200 rows of data
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
this one took about 3 seconds for going through 8200 rows of data
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.