Fastest way to delete rows
 

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
--
Brian

Fastest way to delete rows
 

On 2 Sep, 18:01, Brian wrote:

I think you must post your code here .

Fastest way to delete rows
 

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


--


Gary


"Brian" wrote in message
...
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
--
Brian

Fastest way to delete rows
 

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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

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


--


Gary


"Brian" wrote in message
...
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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

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

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

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
--
Brian


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

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.
--
HTH...

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

Gary,

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

Thanks
--
Brian


"Gary Keramidas" wrote:

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


--


Gary


"Brian" wrote in message
...
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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

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
--
Brian


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Brian" wrote:

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
--
Brian


"Brian" wrote:

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
--
Brian

Fastest way to delete rows
 

On 2 Sep, 19:27, Brian wrote:
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.

* * * * * * * * Rows(c).Delete

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

Fastest way to delete rows
 

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

In article ,
ytayta555 says...

On 2 Sep, 19:27, Brian wrote:
Thanks for the help. =A0I guess I made it too complicated. =A0I'm using m=
y code
to go through once I filtered and deleted the data the first time, and it
works much quicker.

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Rows(c).Delete

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


--
Where are we going and why am I in this HAND BASKET??

Fastest way to delete rows
 

Glad to be usefull ..