A program to delete certain cells

[mribbans]

Hello All,
I am new to the world of Excel VBA so this is quite the learning experience. Currently I am working with an excel spreadsheet that has a set of information which contains both correct and bogus information. The bogus information contains a higher number in a certain column. Here is an example:

Col A Col B
Value1 25
Value1 126 <--bogus
Value2 65
Value2 654 <--bogus

The goal of the macro I wish to create would be to compare the coresponding 'Col B' values of 'Col A' with identical values and delete the lower value row. In other words it would go something like this:

1. Search Worksheet for Rows With Identical 'Col A' Values
2. Compare coresponding 'Col B' values (ex. is Cell 1 > or < Cell 2)
3. Delete Row With Lesser 'Col B' Value
4. Find Next Set of Identical 'Col A' Values
5. Repeat Steps 2-4

Being an extreme novice to this as I said, I haven't the slightest idea as to how to do this. Any help would be much appreciated.

[Bob Phillips]

Popular request today

[vba]

Public Sub DeleteDuplicateRowsUsingAutofilter()
Const TestColumn As Long = 3
Dim cRows As Long

cRows = Cells(Rows.Count, TestColumn - 1).End(xlUp).Row

Columns(TestColumn).Insert
With Cells(2, TestColumn)
.FormulaArray = "=B2=MAX(IF($A$2:$A$" & cRows & "=A2,$B$2:$B$" & cRows & "))"
.AutoFill Destination:=.Resize(cRows - 1)
.Offset(-1, 0).EntireRow.Insert
End With
Columns(TestColumn).AutoFilter Field:=1, Criteria1:="FALSE", Operator:=xlAnd

With Range(Cells(1, TestColumn), Cells(cRows + 1, TestColumn))
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Columns(TestColumn).Delete

End Sub
[/vba]

[mribbans]

Thanks so much. That program worked flawlessly!

[mdmackillop]

Don't forget to mark your thread solved using the thread tools dropdown