Sort and remove duplicates VBA

Question

I want to move values from range S1:S6 to range T1 remove duplicates and sort them from high to low.

Im already able to filter the data and move it to the desired range but i cant figure out how to sort it on the destination range.

Heres what im using to move and filter the data:

Range("S1:S6").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("T1"), Unique:=True

Filter Result

What can i had to this line to sort the data?

Thanks for your time,

Jonhdoe

Can you just sort it after moving it?

JNevill
– JNevill

2018-02-14 19:57:54 +00:00
Commented Feb 14, 2018 at 19:57 — JNevill
– JNevill, Commented Feb 14, 2018 at 19:57

tigeravatar · Accepted Answer · 2018-02-14 20:57:14Z

2

For this solution, it is highly recommended that cell S1 be a header instead of data. As for getting the filter and sort to happen anytime a change is made, use the Worksheet_Change event. Be sure to put this code in a Sheet Module, not a standard module. To get into a Sheet Module, double-click the desired sheet name on the left-hand side of the Visual Basic Editor and that will open that sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rData As Range
    Dim rDest As Range

    'Adjust these as necessary
    Set rData = Me.Range("S1", Me.Cells(Me.Rows.Count, "S").End(xlUp))
    Set rDest = Me.Range("T1")

    'Disable events to prevent infinite loops
    Application.enableevents = False

    'Clear previous results
    rDest.EntireColumn.ClearContents

    'Make sure a change was made in column S
    If Not Intersect(rData, Target) Is Nothing Then
        'Change in column S found, make sure that there is more than 1 cell populated in column S
        If rData.Cells.Count > 1 Then
            'Extract unique values
            rData.AdvancedFilter xlFilterCopy, , rDest, True

            'Sort unique values high to low
            With Me.Range(rDest, Me.Cells(Me.Rows.Count, rDest.Column).End(xlUp))
                .Sort .Cells, xlDescending, Header:=xlYes
            End With
        End If
    End If

    'Re-enable events
    Application.enableevents = True

End Sub

edited Feb 14, 2018 at 20:57

answered Feb 14, 2018 at 20:06

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JonhDoe Over a year ago

Its working as intended but I now realize I explained myself poorly as I also need this filter to be working everytime someone adds data down the copy range, is there a way for this filter to be active at all times?

tigeravatar Over a year ago

@JonhDoe See updated answer, make sure you use a header column (it will reduce a lot of headache if cell S1 is a header, and actual data starts in cell S2) and make sure you put the code in the appropriate sheet's code module (this will not work if it's in a standard module).

Collectives™ on Stack Overflow

Sort and remove duplicates VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related