+ Reply to Thread
Results 1 to 5 of 5

Advanced Filter - Unique Values

  1. #1
    EstherJ
    Guest

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: Advanced Filter - Unique Values

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

    is a possibility
    --
    Regards,
    Tom Ogilvy

    "EstherJ" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Santos_Mohapatra-r47698
    Guest

    Re: 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


  4. #4
    Santos_Mohapatra
    Guest

    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





  5. #5
    Santos_Mohapatra
    Guest

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1