Deleting Rows of Data

[SU]

I have a spreadsheet with 38000 rows of data. The data comes in with date
(01/01/01) in column A and time (12:00:00) in Column B. The data is recorded
every 5 secs.

I would only want to keep data every 5 mins and delete the rest. I gather
this can be done in two ways:

1) by deleting 'x' number of rows below the first data and repeat the process.

2) by matching values **:00:00, **:05:00, **:10:00 and such in Column B and
deleting the rest of the columns.

Can anyone help me with a macro to do this?

Many thanks.

 

[Earl Kiosterud]

Here's a manual way. Add a helper (temporary) column to your table, with a heading (if your
table doesn't have a heading row, this won't work unless you add one). Put the following
formula in the first row below the heading row:

=MOD(MINUTE(B2),5)<>0

All the rows that aren't 5-minute multiples will say TRUE. Now apply an autofilter to your
table (Data - Filter - Autofilter). Filter the helper column for TRUE (Click the helper's
dropdown and select TRUE). This should result in all the rows you don't want. Now select
all the rows (click the row header of the first, then press Ctrl-Shift-DownArrow). Delete
the rows (Edit - Delete, or Ctrl-Minus). Now remove the autofilter (Data - Filter -
Autofilter). Delete the helper column. Done.

 

[Joel]

I'm assume the date in column A and time in column B are in Serial time
format. The dates are whole number and the time is less than 1. You can
easily verify this by changing the format of column A and B to Number (menu
Format - Cells - Number).

Date in excel starts at Jan 1, 1900 and adds one for each day. An hour is
1/24 and a minute is 1/((24*60). five minutes is 5/((24*60) . Becuase your
data is probably running 24 hours a day you need to consider what happens at
midnight.

The easiest way of subtracting 5 minutes from your data is to add the days
and hours together then subtract 5 minutes which I did below. then remove
rows where the time is less than this time.


Sub deleteRows()
Dim Less5 As Double
Dim RowTime As Double

Less5 = Now - (5 / (24 * 60))
RowCount = 1
Do
RowTime = Range("A" & RowCount).value + Range("B" & RowCount).value
If RowTime < Less5 Then
Rows(RowCount).Delete
End If
RowCount = RowCount + 1
Loop While RowTime < Less5

End Sub

 

[SU]

Thank you Earl & Joel. It works.