Advanced Filter - Unique Values
 

I am trying to put together a macro which will run an Advance filter and
bring back unique records. I need it to work on an range which may increase
or decrease. the code below works sometimes but depends I think what cell is
active when the macro is run.

Sub Test()
Dim Irange As Range
Dim ORange As Range
FinalRow = Cells(65536, 1).End(xlUp).Row
NextCol = Cells(1, 255).End(xlToLeft).Column + 2
Range(Selection, Selection.End(xlToRight)).Copy Destination:=Cells(1, NextCol)
Set ORange = Cells(1, NextCol)
Set Irange = Range("a1").Resize(FinalRow, NextCol - 2)
Irange.AdvancedFilter Action:=xlFilterCopy, copytorange:=ORange, Unique:=True
End Sub

Any ideas? Thank you for your help.

Esther

Advanced Filter - Unique Values
 

Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, copytorange:=ORange, Unique:=True

is a possibility
--
Regards,
Tom Ogilvy

"EstherJ" wrote in message
...
I am trying to put together a macro which will run an Advance filter and
bring back unique records. I need it to work on an range which may
increase
or decrease. the code below works sometimes but depends I think what cell
is
active when the macro is run.

Sub Test()
Dim Irange As Range
Dim ORange As Range
FinalRow = Cells(65536, 1).End(xlUp).Row
NextCol = Cells(1, 255).End(xlToLeft).Column + 2
Range(Selection, Selection.End(xlToRight)).Copy Destination:=Cells(1,
NextCol)
Set ORange = Cells(1, NextCol)
Set Irange = Range("a1").Resize(FinalRow, NextCol - 2)
Irange.AdvancedFilter Action:=xlFilterCopy, copytorange:=ORange,
Unique:=True
End Sub

Any ideas? Thank you for your help.

Esther

Advanced Filter - Unique Values
 

Hi Tom,

I have an almost similar requirement.I have two worksheets input and
output.
my input worksheet contains many rows with
"Price" "Item" "vendor" "Vendor Address"

30 Pencil ebay
45 Pencil my_store
75 Pencil other_store
80 Pen ebay
80 Pen my_store
35 Pen other_store
########
Need a Button in output worksheet that will give me only the lowest
values of items in this output worksheet.
input worksheet needs to be unchnaged.
Please help how i can do this.


Tom Ogilvy wrote:
Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, copytorange:=ORange, Unique:=True

is a possibility

Who are in??
 

Hi Vba Gurus,
I have an Attendance application that feeds data dynamically of users
in our office to an excel sheet named input.(Rows added on the fly.

Example as Follows:-

"User_Name" "Time" "IN/OUT" "Location"
Jack 09:30 in 3rd floor Lobby
Joe 09:25 in 3rd Floor Lobby
Jill 07:25 in 5th floor Lobby
Sant 11:00 in 6th floor Gym
Jack 13:00 out 3rd floor Exit
Romi 08:00 in 5th floor lobby
Sushil 09:00 in 4th floor lobby
Joe 11:00 out 5th Floor exit
Romi 14:00 out 6th floor exit
Jack 14:50 in 3rd floor lobby
Joe 15:00 out somewhere
Jill 16:00 out somethingelse
Jack 17:00 out 4th floor exit
Sushil 18:00 out
---
---
################################################## ############
Now i want to update another sheet in same excel workbook named output
based on the above input.

- A button when clicked in this output sheet,Should process the input
worksheet and give me two sections
- Users those are in the building with latest in time and latest location
- Users those who are outside the building with latest out time and exit
point

last status of row data and new worksheet write
 

Hi Vba Gurus,
Please see if you can help me in this.
I have an Attendance application that feeds data dynamically of users
in our office to an excel sheet named worksheet1.(Rows added on the fly.)

Example as Follows:-

"User_Name" "Time" "IN/OUT" "Location"

Jack 09:30 in 3rd floor Lobby
Joe 09:25 in 3rd Floor Lobby
Jill 07:25 in 5th floor Lobby
Sant 11:00 in 6th floor Gym
Jack 13:00 out 3rd floor Exit
Romi 08:00 in 5th floor lobby
Sushil 09:00 in 4th floor lobby
Joe 11:00 out 5th Floor exit
Romi 14:00 out 6th floor exit
Jack 14:50 in 3rd floor lobby
Joe 15:00 out somewhere
Jill 16:00 out somethingelse
Jack 17:00 out 4th floor exit
Sushil 18:00 out 6th floor exit
Jack 19:00 in 4th floor exit
Sant 20:00 in 6th floor Gym


---
---
################################################## ############
Now i want to know the latest (last) status of users based upon current
time to be updated in worksheet2.
This worksheet2 need to be updated as and when new rows are added in
input worksheet1.
OR
- A button when clicked in this worksheet2,Should process the input
worksheet1 and write to worksheet2.

########### Worksheet2 Sample output#######

INS
############################################

"User_Name" "Time" "IN/OUT" "Location"
Jack 19:00 in 4th floor exit
Sant 20:00 in 6th floor Gym

OUTS
################################################## ##
Joe 15:00 out somewhere
Jill 16:00 out somethingelse
Romi 14:00 out 6th floor exit
Sushil 18:00 out 6th floor exit