Select the first two similar values

[Klartigue]

A1
A1
A1
A2
A2
A2
A2
A3
A3
A3
A..

A1, A2, A3, etc.. represents different bids for one item. For example, A1 is an item and each row for A1 represents a different bid. I am trying to write a macro that selects only the first two A1, the first two A2, etc..

Thanks for your help

[Bob Phillips]

Off the top


With Activesheet
Lastrow = .Cells(.Rows.Count,"A"),End(xlUp).Row
For i = LastRow To 3 Step -1
If Application.Countif(.Columns(1),.Cells(i, "A").Value) > 2 Then
.Rows(i).Delete
End If
Next i
End With

[Klartigue]

Thanks. Now what if there are more than 3 items, it could go from A1 to A30, etc...

Will the code above work?

[Bob Phillips]

Why don't you try it and see.

[Klartigue]

Lastrow = .Cells(.Rows.Count,"A"),End(xlUp).Row

Excel is saying there is a syntax error with the above line??

Thanks for the help

[Bob Phillips]

Typo, it should be


Lastrow = .Cells(.Rows.Count,"A").End(xlUp).Row

[Klartigue]

It works, thank you! I just have a couple more questions with this project.

So now I have:

A1
A1
A2
A2
A3
A3
etc..

How do I get a macro to highlight in yellow the top A1, top A2, top A3, and rename the bottom A1, A2, and A3 the word "cover"

[Bob Phillips]

Surely, after running the code, there is only one of each.

[Klartigue]

Sub highlight()
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
If Application.CountIf(.Range("A1").Resize(i), .Cells(i, "A").Value) = 2 Then


i know it starts like this..i just dont know how to rename a cell using a macro

[Klartigue]

In the cell located in in column R, row 1 I would like to put the word "EVAL" ??

Thanks for your help

[Klartigue]

Sub NameEval()
Range("R1").Select
ActiveCell.Value = "EVAL"
End Sub

Nevermind I figured it out!!

[Bob Phillips]

You don't need to select



Sub NameEval()

Range("R1").Value = "EVAL"

End Sub