Sunday, July 20, 2008

Create Advanced Filter in Excel VBA for making the range with unique records

Make Range Unique using Advanced Filter in Excel VBA

Advanced Filter cane be used for making the range with unique records. The following snippet should be useful for the same.

Sub Make_Unique_List_InPlace()

Dim oWS As Worksheet ' Worksheet Object

Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

On Error GoTo Disp_Error

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

Set oRange = Range("B:B")

oRange.AdvancedFilter(Action:=xlFilterInPlace, Unique:=True)

If Not oRange Is Nothing Then oRange = Nothing

If Not oWS Is Nothing Then oWS = Nothing

' --------------------

' Error Handling

' --------------------

Disp_Error:

If Err <> 0 Then

MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")

Err.Clear()

Resume Next

End If

End Sub

oRange.AdvancedFilter filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used. The above code does not physically delete duplicate records from the sheet/range


6 comments:

  1. Anonymous4:59 AM

    you have omitted the Set keyword in the following command:
    oRange = Range("B:B")

    ReplyDelete
  2. Thanks, It was because of the formatting I did it in VS2008 - was smart enough to remove 'Set'. I have corrected it now

    ReplyDelete
  3. Here is working code with changes I made to work in Excel 2007 and include retrieving values of the filtered range.

    Sub Make_Unique_List_InPlace()

    Dim oWS As Worksheet ' Worksheet Object

    Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

    On Error GoTo Disp_Error

    ' ---------------------------------------------

    ' Coded by Shasur for www.vbadud.blogspot.com

    ' ---------------------------------------------

    'Demo1 is a defined name
    Set oRange = Range("Demo1")


    oRange.AdvancedFilter xlFilterInPlace, , , True


    Dim ocell As CellFormat



    If Not oRange Is Nothing Then

    For Each Item In oRange

    MsgBox Item.Value

    Next

    oRange = Nothing

    End If


    If Not oWS Is Nothing Then oWS = Nothing

    ' --------------------

    ' Error Handling

    ' --------------------

    Disp_Error:

    If Err <> 0 Then

    MsgBox Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples"


    Err.Clear



    Resume Next

    End If

    End Sub

    'oRange.AdvancedFilter filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used. The above code does not physically delete duplicate records from the sheet/range

    ReplyDelete
  4. Be aware, that Excel 2007 requires intenational notation if advanced filter is used in VBA. For Manual filtering "< 0,1" is OK as condition on a german Computer. But for use in VBA "< 0.1" has to be the contents of the cell.
    So in VBA Excel 2003 and 2007 behave differently.

    ReplyDelete
  5. Anonymous8:21 AM

    Why the Worksheet object if ain't used?

    ReplyDelete
  6. Anonymous1:11 AM

    lol

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group