Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
I have around 30 spreadsheets with a series of values including those
shown below. I need to remove all rows with the 5 minute entries and keep those with the 10 minute entries. From this: 30/09/2018 07:15 23959.093 0 30/09/2018 07:20 23959.093 0 30/09/2018 07:25 23959.093 0 30/09/2018 07:30 23959.093 0 30/09/2018 07:35 23959.094 0.012 30/09/2018 07:40 23959.099 0.06 30/09/2018 07:45 23959.108 0.108 30/09/2018 07:50 23959.118 0.12 30/09/2018 07:55 23959.123 0.06 30/09/2018 08:00 23959.127 0.048 30/09/2018 08:05 23959.132 0.06 30/09/2018 08:10 23959.138 0.072 30/09/2018 08:15 23959.145 0.084 30/09/2018 08:20 23959.153 0.096 30/09/2018 08:25 23959.162 0.108 30/09/2018 08:30 23959.173 0.132 30/09/2018 08:35 23959.185 0.144 30/09/2018 08:40 23959.199 0.168 30/09/2018 08:45 23959.215 0.192 30/09/2018 08:50 23959.234 0.228 30/09/2018 08:55 23959.256 0.264 30/09/2018 09:00 23959.28 0.288 30/09/2018 09:05 23959.306 0.312 30/09/2018 09:10 23959.336 0.36 30/09/2018 09:15 23959.366 0.36 30/09/2018 09:20 23959.4 0.408 30/09/2018 09:25 23959.434 0.408 To this: 30/09/2018 07:20 23959.093 0 30/09/2018 07:30 23959.093 0 30/09/2018 07:40 23959.099 0.06 30/09/2018 07:50 23959.118 0.12 30/09/2018 08:00 23959.127 0.048 30/09/2018 08:10 23959.138 0.072 30/09/2018 08:20 23959.153 0.096 30/09/2018 08:30 23959.173 0.132 30/09/2018 08:40 23959.199 0.168 30/09/2018 08:50 23959.234 0.228 30/09/2018 09:00 23959.28 0.288 30/09/2018 09:10 23959.336 0.36 30/09/2018 09:20 23959.4 0.408 Any help would be appreciated! -- F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
Hi,
Am Tue, 27 Nov 2018 12:17:32 +0000 schrieb F: I have around 30 spreadsheets with a series of values including those shown below. I need to remove all rows with the 5 minute entries and keep those with the 10 minute entries. From this: 30/09/2018 07:15 23959.093 0 30/09/2018 07:20 23959.093 0 30/09/2018 07:25 23959.093 0 To this: 30/09/2018 07:20 23959.093 0 30/09/2018 07:30 23959.093 0 30/09/2018 07:40 23959.099 0.06 with your time in column A try: Sub DeleteRows() Dim LRow As Long, i As Long Dim wsh As Worksheet For Each wsh In Worksheets With wsh LRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LRow To 1 Step -1 If Minute(.Cells(i, "A")) Mod 10 = 5 Then .Rows(i).Delete End If Next End With Next End Sub Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
Thank you! That does exactly what I need to do (though it does complain
with a Run-time error '13': Type mismatch and Debug highlights 'If Minute(.Cells(i, "A")) Mod 10 = 5 Then'). -- F On 27/11/2018 12:57, Claus Busch wrote: Hi, Am Tue, 27 Nov 2018 12:17:32 +0000 schrieb F: I have around 30 spreadsheets with a series of values including those shown below. I need to remove all rows with the 5 minute entries and keep those with the 10 minute entries. From this: 30/09/2018 07:15 23959.093 0 30/09/2018 07:20 23959.093 0 30/09/2018 07:25 23959.093 0 To this: 30/09/2018 07:20 23959.093 0 30/09/2018 07:30 23959.093 0 30/09/2018 07:40 23959.099 0.06 with your time in column A try: Sub DeleteRows() Dim LRow As Long, i As Long Dim wsh As Worksheet For Each wsh In Worksheets With wsh LRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LRow To 1 Step -1 If Minute(.Cells(i, "A")) Mod 10 = 5 Then .Rows(i).Delete End If Next End With Next End Sub Regards Claus B. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
Hi,
Am Tue, 27 Nov 2018 15:22:31 +0000 schrieb F: Thank you! That does exactly what I need to do (though it does complain with a Run-time error '13': Type mismatch and Debug highlights 'If Minute(.Cells(i, "A")) Mod 10 = 5 Then'). are there also other sheets in the workbook without the times in columns A? Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
Ah! Yes! Well spotted! Apologies, the times start in A9.
-- Frank On 27/11/2018 16:56, Claus Busch wrote: Hi, Am Tue, 27 Nov 2018 15:22:31 +0000 schrieb F: Thank you! That does exactly what I need to do (though it does complain with a Run-time error '13': Type mismatch and Debug highlights 'If Minute(.Cells(i, "A")) Mod 10 = 5 Then'). are there also other sheets in the workbook without the times in columns A? Regards Claus B. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
Hi,
Am Tue, 27 Nov 2018 17:43:55 +0000 schrieb F: Ah! Yes! Well spotted! Apologies, the times start in A9. then change this line: For i = LRow To 1 Step -1 to For i = LRow To 9 Step -1 Regards Claus B. -- Windows10 Office 2016 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows depending on time value
On 27/11/2018 18:04, Claus Busch wrote:
Hi, Am Tue, 27 Nov 2018 17:43:55 +0000 schrieb F: Ah! Yes! Well spotted! Apologies, the times start in A9. then change this line: For i = LRow To 1 Step -1 to For i = LRow To 9 Step -1 Regards Claus B. Thank you! -- F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows if time is greater than present time | Excel Programming | |||
delete the non blank rows depending on Column | Excel Programming | |||
Delete variable # of rows depending on conditions | Excel Programming | |||
Delete rows depending on cell Value | Excel Programming | |||
How to delete a set of rows depending on Value | Excel Discussion (Misc queries) |