Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a group of Chart tabs | Charts and Charting in Excel | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
How can i copy a group of cells in a row to a column? | Excel Discussion (Misc queries) | |||
Copy range excluding group data | Excel Programming | |||
Copy range excluding group data | Excel Programming |