Sorting for Unique records using VBA

[K. Georgiadis]

I know how to use Excel's built in Advanced Filtering to sort for unique records. I was wondering if there is a VBA procedure for accomplishing this. Say that the data range is A2:K126, with row A2 containing the headings and each subsequent row constituring a record. The objective would be to select the unique records out of the data range and display them in a new worksheet "Unique Records."

You might say that this post is a matter of curiosity rather than an urgent need.

PS: Ooops! I forgot that the filtered data can only be copied to the active worksheet. Also, I think that I can answer my own question by performing the advanced filtering and recording the steps.

[Jacob Hilderbrand]

You can us CountIf to check if the cell is unique. Something like this.



Option Explicit

Sub GetUnique()
Dim i As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Application.WorksheetFunction.CountIf(Range("A1:A" & i), Range("A" & i).Text) = 1 Then
Range("B65536").End(xlUp).Offset(1, 0).Value = Range("A" & i).Text
End If
Next i
End Sub


This will put all the unique values in column A in column B.

[K. Georgiadis]

and the answer is....

With the data in range A3:I149 of sheet named "Pivot Data" and the copy destination starting at cell A56:


Sub filter_unique_records()
Sheets("Pivot Data").Select
Range("A3:I49").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"A56"), Unique:=True
End Sub

[K. Georgiadis]

thanks Jake! How would you use COUNTIF if you are testing an entire row as a unique record, not just a single cell?