| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fastest way to delete rows
On 2 Sep, 18:01, Brian wrote:
I think you must post your code here . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros or the Fastest way to Autofill Rows | Excel Discussion (Misc queries) | |||
| Repeating a Cell Format For Many Rows: What's Fastest Way? | Excel Discussion (Misc queries) | |||
| How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
| Fastest Way to Filter/Delete | Excel Worksheet Functions | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
Hybrid Mode
