Delete rows based on max value

[Ron512]

I would appreciate assistance with a macro that would delete all rows with the same value in Column A that have a value less than the max value in column B.

Thanks

Ron

Sample Data

A	B
Florida	200
Florida	210
Florida	150
Georgia	130
Georgia	151

]

Desired result

A	B
Florida	210
Georgia	151

]

 

[JoeMo]

This assumes a layout (with headers) as shown below:

Book1
	A	B
1	State	Value
2	Florida	200
3	Florida	210
4	Florida	150
5	Georgia	130
6	Georgia	151
Sheet1 (5)

Sub Ron512()
Dim R As Range
Set R = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
With R
    .Sort key1:=[B2], order1:=xlDescending, Header:=xlYes
    .RemoveDuplicates Columns:=1, Header:=xlYes
End With
    
End Sub

 

[Ron512]

Joe, Thanks for the quick reply. It works perfectly based on the criteria I provided. After a look at the complete data set and putting more thought into the required results the task is more complicated. I will put together a better description of the criteria and sample data. Do you suggest I continue in this thread or mark it solved and start a new one?

 

[JoeMo]

Joe, Thanks for the quick reply. It works perfectly based on the criteria I provided. After a look at the complete data set and putting more thought into the required results the task is more complicated. I will put together a better description of the criteria and sample data. Do you suggest I continue in this thread or mark it solved and start a new one?

You are welcome.

You can continue this thread if the thread title remains reasonably descriptive.

Whatever you do, please use XL2BB to post the sample data .... makes it much easier to copy the data for testing w/o typing.
XL2BB - Excel Range to BBCode