Macro Help - Find Max Value in Multiple Ranges Then Delete Non-Max Rows

[MrEE]

Hello,
I love excell (even though I am not near an expert) and just now found this forum when I was searching for a solution to my problem. So hello forum! Here is my question...

I have a massive data sheet with values incremiting by 2 seconds. Example:

Column A Column B
00:00:02 200
00:00:04 200
00:00:06 300
00:00:08 300
00:00:10 300
00:00:12 200

I have 2 second data for 7 days like this (lots of data!)

What I am wanting: I am trying to write a macro (or maybe use a formula) to determine the maximum value for each minute, then either export that max value OR delete all non-max value rows.
This will therefore shrink my 302,400 row worksheet down to 5,040 rows.

Wanting:
Column A Column B
00:00:xx 300
00:01:xx 300
00:02:xx 200
00:03:xx 175

The seconds do not matter to me. Just the minute.

The values in column B do not change a lot. Therefore, the values in column B could all be the same for a duration of a minute.

Anyone have any suggestions? Thanks!

 

[vaskov17]

Don't have vba right now so I can't give you actual code but what I would do is
x=last row
for x=last row to 2 step -1
if hour(x)=hour(x-1) and minute (x)=minute(x-1) then
if data(x)<data(x-1) then
delete row(x)
x=x-1
else
delete row(x-1)
x=x-1
end if
next x

 

[MrEE]

I won't lie, I am not good at programming macros. Could you give me alittle more insight?

 

[vaskov17]

The "code" above means that you:
- start at the last row and count back to the beginning, that's why x=last row
- compare the minute and possibly hour values for last row and next to last row
- if they are equal, then compare the data and delete the row with the lower value and decrease x by 1
- if they are not equal, then decrease x by 1

Like I said, I don't have vba in front of me so I can't give you the actual code

 

[MrEE]

Here is what I have so far...

Sub Trying()
'
Dim x As Long
Dim activecell As String
Dim column() As Integer

x = last.Row

activecell = x.Offset(-1).Select.EntireRow
If Hour(x) = Hour(activecell) And Minute(x) = Minute(activecell) Then
If column(x) = column(activecell) Then Selection.EntireRow.Delete shift:=xlUp
x = x.Offset(-1, 0).Select
Else

x = x.Offset(-1, 0).Select
End If

End Sub


I am getting all kinds of errors. The first one being "Object Required"