Try this which assumes data starts in row 2, column A. Change worksheets as
required.

Sub ExtractRows()
Dim rnga As Range
Dim lastrow As Long, r As Long, n As Long
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rnga = .Range("a2:a" & lastrow)
r = 2
Do
n = Application.CountIf(rnga, .Cells(r, "A"))
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp)(2) = .Cells(r,
4)
r = r + n
Loop Until r lastrow
End With
End Sub

HTH

"sa02000" wrote:


I have data in following format. Data is sorted by column1 first and
Column3 second. Now I would like to extract values in column4 for the
rows with max value in column3 for a given group in column1. So in the
data below I would like to extract rows 1,4 and 9. Obviously I have
thousands of rows with different sets of data to do this for.

Column1 Column2 Column 3 Column 4
A 567 5 abc
A 568 2 gbv
A 453 1 gfh
B 546 25 ght
B 256 15 rte
B 265 5 rtw
B 325 2 tyr
B 315 1 riu
C 246 56 ade
C 234 55 jff
C 342 32 hgf
C 651 12 dfd


Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=520616