Edit Macro *SELF SOLVED*

scrupul0us

Well-known Member
Joined
Sep 7, 2004
Messages
641
How would i chagne this:

Code:
Sub Unique_DEPT()
    Dim Sh1 As Worksheet
    Dim Rng As Range
    Dim Sh2 As Worksheet
    Set Sh1 = Worksheets("DATA")
    Set Rng = Sh1.Range("L2:L" & Sh1.Range("A65536").End(xlUp).Row)
    Set Sh2 = Worksheets("RESULTS")
    Rng.Cells(1, 1).Copy Sh2.Cells(1, 2)
    Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh2.Range("B1"), Unique:=True
End Sub

to list my values across row1 instead of down column b?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Sharkie21

Active Member
Joined
Nov 2, 2005
Messages
319
Two ideas come to mind. 1) create a sql query to find unique entries given the list.

2) Sort the list and find the unique entries yourself and paste them into the next worksheet.

So something like
'Assume it's sorted
private sub blah()
currRow as int, prevRow as int
currRow = 2
prevRow = 1
while prevRow.value <> ""
'no duplicate
if prevRow.value <> currRow.value then
'put prevRow.value into sheet 2 or where ever
end if
prevRow + 1
currRow + 1
end sub

I think that should get all the unique entries in linear time.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,113
Messages
5,570,266
Members
412,314
Latest member
yazanwael
Top