Same operations on a variable number of rows
On Wed, 6 Apr 2005 10:38:08 -0400, in microsoft.public.excel , "Tom
Ogilvy" in
wrote:
assuming data starts in A1 and there are no headers in the first row.
Sub GetUniques()
Dim rng As Range
Dim cell As Range
Dim rng1 As Range
Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each cell In rng
Cells(cell.Row, "C").Value = cell.Value & _
cell.Offset(0, 1).Value
Next
Cells(1, "C").Insert Shift:=xlShiftDown
Cells(1, "C").Value = "Header"
Set rng1 = Range(Cells(1, "C"), Cells(1, "C").End(xlDown))
rng1.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Cells(1, "D"), _
Unique:=True
Cells(1, "C").Resize(1, 2).Delete Shift:=xlShiftUp
End Sub
This method doesn't use concatenation and the results are not concatenated,
however the unique results are the same if you did concatenate them.
I think I understand this enough to make it do what I want. Thanks for
a very large help.
Sub GetUniquesAlternate()
Range("A1:B1").Insert Shift:=xlDown
Range("A1:B1").Value = _
Array("Header1", "Header2")
Range("A1").CurrentRegion.Resize(, 2) _
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Cells(1, "C"), _
Unique:=True
Range("A1:D1").Delete Shift:=xlShiftUp
End Sub
More compact, but harder for me to do more processing. Thanks again.
--
Matt Silberstein
All in all, if I could be any animal, I would want to be
a duck or a goose. They can fly, walk, and swim. Plus,
there there is a certain satisfaction knowing that at the
end of your life you will taste good with an orange sauce
or, in the case of a goose, a chestnut stuffing.
|