Fastest way to delete rows

[Brian]

Hello All,

I have a group of data that was recorded 6 times every minute for a year. I
need to only keep one row at the top of each hour. I wrote a script and have
it running now. It checks each row and if the minute in that row is not 0
then it deletes it, if it is zero it deletes the next 5 rows then skips one
row. I've had it running for about 10 minutes now and I'm through with about
a day of data. Is there a faster way of doing it?

Thanks

 

[ytayta555]

I think you must post your code here .

 

[Gary Keramidas]

make sure you turn off calculation and screen updating before running your code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' your code

Application.ScreenUpdating =True
Application.Calculation = xlCalculationAutomatic

 

[Brian]

Here is the code I'm using now:

For c = 1 To 65536
If Range("V" & c).Value = "" Then
Exit Sub
Else
If Range("V" & c).Value <> 0 Then
Rows(c).Delete
c = c - 1
Else
t = 1
For t = 1 To 5
Rows(c).Delete
Next t
End If
End If
Next c

 

[Gary Keramidas]

try this and see if it works, just change the worksheet name in the code

Sub test()
Dim lastrow As Long
Dim c As Long
Dim t As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "V").End(xlUp).Row
For c = lastrow To 1 Step -1
If Range("V" & c).Value <> 0 Then
Rows(c).Offset(-4).Resize(5).Delete
c = c - 5
Else
'do nothing
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Jim Thomlinson]

Why not just sort the data by the minute coumn and delete the non-zero entries?

 

[Brian]

That get's rid of probably 99.9% of the data I want to get rid of, but it
still leaves me with 6 entries for each hour since the data was taken 6 times
a minutes. How do I get rid of all but one of the hourly data?

Thanks

 

[Jim Thomlinson]

Add an extra column of data. Put in the numbers 1,2,3,4,5,6,1,2,3,...
now you have a repeating list of the numbers 1 - 6. Sort by this column to
float the 1's to the top... Delete the entries that are 2 - 6.

 

[Brian]

Gary,

I get an Application Defined Error 1004 on the Delete Row.

Thanks

 

[Brian]

Jim,

Thanks for the help. I guess I made it too complicated. I'm using my code
to go through once I filtered and deleted the data the first time, and it
works much quicker.

Thanks again

 

[ytayta555]

Thanks for the help.  I guess I made it too complicated.  I'm using my code
to go through once I filtered and deleted the data the first time, and it
works much quicker.

I have found that for me method ClearContents work faster then
Delete .
Am I right ?

 

[plh]

Hey ytayta555,
I gotta thank you for that, because I was having the same problem with deleting
rows, but was able to find a work around using ClearContents, which turned
minutes into seconds.
-plh

 

[ytayta555]

Glad to be usefull ..