Make Range
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
Posts
you have omitted the Set keyword in the following command:
ReplyDeleteoRange = Range("B:B")
Thanks, It was because of the formatting I did it in VS2008 - was smart enough to remove 'Set'. I have corrected it now
ReplyDeleteHere is working code with changes I made to work in Excel 2007 and include retrieving values of the filtered range.
ReplyDeleteSub 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
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.
ReplyDeleteSo in VBA Excel 2003 and 2007 behave differently.
Why the Worksheet object if ain't used?
ReplyDeletelol
ReplyDelete