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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top