techissue2008
Board Regular
- Joined
- Jun 13, 2008
- Messages
- 80
Hi
I have a sheet1:
(A1) 1 (B1) ABC (C1) 90
(A2) 1 (B2) DEF (C2) 80
(A3) 2 (B3) ABC (C3) 70
(A3) 2 (B3) DEF (C3) 60
I want to present it in sheet2 to be
(A1) (B1) 1 (C1) 2
(A2) ABC (B2) 90 (C2) 70
(A3) DEF (B3) 80 (C3) 60
(A:A) needs to sort by name
How can I code it?
Here is the sorting code:
It shows the text in A1, but not A2.
Thanks
I have a sheet1:
(A1) 1 (B1) ABC (C1) 90
(A2) 1 (B2) DEF (C2) 80
(A3) 2 (B3) ABC (C3) 70
(A3) 2 (B3) DEF (C3) 60
I want to present it in sheet2 to be
(A1) (B1) 1 (C1) 2
(A2) ABC (B2) 90 (C2) 70
(A3) DEF (B3) 80 (C3) 60
(A:A) needs to sort by name
How can I code it?
Here is the sorting code:
Code:
Sub test()
Dim nodupes As New Collection
With Sheets("Sheet1")
For Each ce In .Range("A1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
On Error Resume Next
nodupes.Add Item:=ce.Value, key:=ce.Value
On Error GoTo 0
Next ce
End With
With Sheets("Sheet2")
.Cells.ClearContents
For i = 1 To nodupes.Count
.Cells(i, "A").Value = nodupes(i)
Next i
.Range("a1").Resize(nodupes.Count).Offset(, 1).Formula = "=len(a1)"
.Range("A1", .Range("A1").End(xlDown)).Resize(, 2).Sort key1:=.Range("B1"), Order1:=xlAscending, header:=xlNo
.Columns("b").Delete
End With
End Sub
It shows the text in A1, but not A2.
Thanks