Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 27th 18, 01:17 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2011
Posts: 46
Default 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   Report Post  
Old November 27th 18, 01:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,632
Default 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   Report Post  
Old November 27th 18, 04:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2011
Posts: 46
Default 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   Report Post  
Old November 27th 18, 05:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,632
Default 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   Report Post  
Old November 27th 18, 06:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2011
Posts: 46
Default 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   Report Post  
Old November 27th 18, 07:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,632
Default 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   Report Post  
Old December 7th 18, 12:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2011
Posts: 46
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete rows if time is greater than present time Sabosis Excel Programming 2 January 14th 13 03:12 PM
delete the non blank rows depending on Column Harn88 Excel Programming 1 November 18th 08 09:37 AM
Delete variable # of rows depending on conditions Pman Excel Programming 5 September 27th 07 03:27 PM
Delete rows depending on cell Value Tomas Stroem Excel Programming 3 June 8th 07 10:36 AM
How to delete a set of rows depending on Value msbutton27 Excel Discussion (Misc queries) 1 January 15th 06 05:57 PM


All times are GMT +1. The time now is 08:04 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017