Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Advanced autofilter in vba - Can't Get Unique Values

Status
Not open for further replies.

bartrein

Programmer
Sep 22, 2008
49
EU
Hi

- I have a table of data in range a1:d20000
- Column "B" contains duplicate & unique values
- I want to delete entire-rows with duplicates in col B
- TO do that i create a temporary sheet,
using ADVANCED autofilter i extract and paste rows with unique values into that sheet, remove original table with duplicates and paste back unique records

So far so good, it works great and quick on large volumes of data except that sometimes it doesn't work :)

Can't think of any reason why this is happening but i came across the following scenarios :

- my macro works always on one column
- usually works on more columns providing criteria column (the one filtered for unique values) is first (leftmost)
- only sometimes works when criteria column is in between other columns

The last 2 points are really confusing me because i am unable to establish a pattern here.
The source data is a standard table, no blank rows,column b
contains alfanumeric codes e.g 027293247X.LU, i know that
autofilter assumes that column has a header and i'm ok with that. The problem is that sometimes filtering does not happen at all and after this line pasted records are exactly the same (with duplicates):

'''''''''''''''''''''''''''''''''''''''''''''

rngDataTable.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rngCriteriaColumn, _
CopyToRange:=rngPasteUniqueRecs, _
Unique:=True

''''''''''''''''''''''''''''''''''''''''

Am i missing something obvious here?
Can you good people help me out of my misery?

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Sub test_delDupl()
'
' Macro1 Macro
' Macro recorded 26/8/2009 by bartosz_reinholc
Dim rngTable As Range
Dim rngCriteria As Range
Dim t


t = Timer
Set rngTable = Sheets("source").Range("a1:b1629")
Set rngCriteria = Sheets("source").Range("b1:b1629")

DeleteRows_DUPLICATES rngTable, rngCriteria

Set rngTable = Nothing
Set rngCriteria = Nothing

Debug.Print Timer - t


End Sub

'---------------------------------------------------------------------------------------
' Procedure : DeleteRows_DUPLICATES
' Author :
' Date : 8/26/2009
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Sub DeleteRows_DUPLICATES(rngDataTable As Range, _
rngCriteriaColumn As Range)

On Error GoTo DeleteRows_DUPLICATES_Error

'create new sheet to paste filtered data
Dim shTempSheet As Worksheet
Set shTempSheet = Sheets.Add

'set ref to destination range in the new sheet....
'...where data will be pasted
Dim rngPasteUniqueRecs As Range
Set rngPasteUniqueRecs = shTempSheet.Range("a1")

'check if passed parameters are valied excel ranges
If rngDataTable Is Nothing _
Or rngCriteriaColumn Is Nothing _
Or rngPasteUniqueRecs Is Nothing Then

MsgBox "Invalid Range", vbCritical, "Error"
Exit Sub
End If


'Use Advance filter to get unique values and paste them into new sheet
rngDataTable.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rngCriteriaColumn, _
CopyToRange:=rngPasteUniqueRecs, _
Unique:=True

'remove all values from the original range (containing duplicates)
With rngDataTable
.ClearContents
.Value = shTempSheet.UsedRange.Value 'paste filtered unique rows
End With

'delete temp sheet
rngDataTable.Application.DisplayAlerts = False
shTempSheet.Delete
rngDataTable.Application.DisplayAlerts = True

'free memory
Set rngPasteUniqueRecs = Nothing

On Error GoTo 0
Exit Sub

DeleteRows_DUPLICATES_Error:
'free memory
Set rngPasteUniqueRecs = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteRows_DUPLICATES of Module Module1"

End Sub

 



Hi,

Here's how to remove duplicates easily.

Put a COUNTIF formula in a helper column. If the returned value is greater than 1, you have a duplicate...
[tt]
=COUNTIF($B$2:B2,B2)
[/tt]
Use AutoFilter to display rows > 1

Select rows & DELETE.

Record a macro if you think you need code.

VOLA y'all!


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 


Alternatively, you could also use the Advanced Filter to get unique values for the table. Again, no VBA required.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks Skip

I didn't explain properly what i wanted
I want to extract UNIQuE values (so if something appears twice on my list it will only appear once after the macro)

The reason for VBA is that i want this routine to be part of a larger structure of code and i wanted to write it in a way that i would be reusable (just pass diff source and criteria range).

DO you see anything wrong in my code that could be causing problems?

many thanks
 


and why are you using ANY criteria? You do not need a criteria to filter qnique values.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I might be wrong but i thought that by specifying criteria range you tell the filter in which column to look for unique values?
 



Do you mean to say that in column B, you have the SAME value, but the other columns, like C, have DIFFERENT values? Then how can you delete data base on column B, when there is non-uniqueness in other columns? WHICH ONE TO DELETE?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
The original table:

ABE.MC B 125
ABF.L B 126
ABZ.L A 127
ABG.MC A 128
ABR.MC B 129

And what i would like it to look like after filtering :

ABE.MC B 125
ABZ.L A 127

 


So you don't care WHICH of these two are deleted, as long as you end up with one?
[tt]
ABF.L B 126
ABR.MC B 129
[/tt]

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 


Then use my COUNTIF technique, since you do not really have rows with unique values.

My result, in 15 seconds...
[tt]
colB ColC ColD Dups
ABE.MC B 125 1
ABZ.L A 127 1
[/tt]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 



Please post what criteria you used in your advanced filter.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Hey Skip

I've finally done some proper homework on this advancedfilter tool and now got a bit better idea.
The key thing was that I didn't realize that criteria property works in the same way as in data functions (DSUM etc), i initially thought that it would do the same as autofilter i.e. filter whole table using values from specified column.
The whole excercise was actually quite useful for me i've learnt how to :
- delete duplicate rows (unique , no criteria)
- delete rows with speicific value in the specific column (critria =, >, < , <>)
Using this tool i can achieve both much faster than using my previous ways (specialcells, formulas or loops)

Thank you for taking time to respond to my questions
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top