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

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

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.

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

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.

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

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