Copy only first row of each group.
 

Hi,
I have the following sample sheet:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
ZY1 WZ45 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35
0.45
XY1 ZZ03 0.25 0.35
0.45
ABC AB05 0.25 0.35 0.45
ABC AB07 0.25 0.35 0.45

What I would like to get is the following:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35 0.45
ABC AB05 0.25 0.35 0.45

Basically, would like to get just the first item of each GROUP and copy the
whole row to another sheet

can this be done?

Please advise any help/feedback would appreciate it.

Thanks,

juan

Copy only first row of each group.
 

Assuming your data starts in cell A1 and is sorted by column A, try this:

Sub MoveFirst()
Dim lRow As Long
Dim cRow As Long
Dim lCol As Integer
Dim colA As Range
Dim Cell As Range
Dim newSht As Worksheet
Dim tSht As Worksheet

Set tSht = ActiveSheet
Set newSht = Sheets.Add
With tSht
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set colA = Range(.Cells(2, 1), .Cells(lRow, 1))
Range(.Cells(1, 1), .Cells(1, lCol)).Copy _
newSht.Range("A1")
cRow = 2
For Each Cell In colA
If Cell.Value < Cell.Offset(-1, 0).Value Then
Range(.Cells(Cell.Row, 1), .Cells(Cell.Row, lCol)).Copy _
newSht.Cells(cRow, 1)
cRow = cRow + 1
End If
Next Cell
End With
End Sub

Hope this helps
Rowan

"Juan" wrote:

Hi,
I have the following sample sheet:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
ZY1 WZ45 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35
0.45
XY1 ZZ03 0.25 0.35
0.45
ABC AB05 0.25 0.35 0.45
ABC AB07 0.25 0.35 0.45

What I would like to get is the following:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35 0.45
ABC AB05 0.25 0.35 0.45

Basically, would like to get just the first item of each GROUP and copy the
whole row to another sheet

can this be done?

Please advise any help/feedback would appreciate it.

Thanks,

juan

Copy only first row of each group.
 

Hello Rowan,
thanks alot This IS greatttt..

Really appreciated your help.

Thanks again,

Juan

"Rowan" wrote:

Assuming your data starts in cell A1 and is sorted by column A, try this:

Sub MoveFirst()
Dim lRow As Long
Dim cRow As Long
Dim lCol As Integer
Dim colA As Range
Dim Cell As Range
Dim newSht As Worksheet
Dim tSht As Worksheet

Set tSht = ActiveSheet
Set newSht = Sheets.Add
With tSht
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set colA = Range(.Cells(2, 1), .Cells(lRow, 1))
Range(.Cells(1, 1), .Cells(1, lCol)).Copy _
newSht.Range("A1")
cRow = 2
For Each Cell In colA
If Cell.Value < Cell.Offset(-1, 0).Value Then
Range(.Cells(Cell.Row, 1), .Cells(Cell.Row, lCol)).Copy _
newSht.Cells(cRow, 1)
cRow = cRow + 1
End If
Next Cell
End With
End Sub

Hope this helps
Rowan

"Juan" wrote:

Hi,
I have the following sample sheet:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
ZY1 WZ45 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35
0.45
XY1 ZZ03 0.25 0.35
0.45
ABC AB05 0.25 0.35 0.45
ABC AB07 0.25 0.35 0.45

What I would like to get is the following:
GROUP PART# 20040923 20050301 20050404 20050512 20050527
ZY1 WX34 0.0247 0.0252 0.0252
XY1 ZZ01 0.25 0.35 0.45
ABC AB05 0.25 0.35 0.45

Basically, would like to get just the first item of each GROUP and copy the
whole row to another sheet

can this be done?

Please advise any help/feedback would appreciate it.

Thanks,

juan