tag:blogger.com,1999:blog-28492262.post6497281679399527615..comments2023-12-27T22:22:25.151-08:00Comments on VBA Tips & Tricks: Create Advanced Filter in Excel VBA for making the range with unique recordsUnknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-28492262.post-20258360575923338952011-03-30T01:11:51.823-07:002011-03-30T01:11:51.823-07:00lollolAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-12263838823783411502009-10-20T08:21:08.549-07:002009-10-20T08:21:08.549-07:00Why the Worksheet object if ain't used?Why the Worksheet object if ain't used?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-32335702269454798542009-04-24T02:26:00.000-07:002009-04-24T02:26:00.000-07:00Be aware, that Excel 2007 requires intenational no...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.<br />So in VBA Excel 2003 and 2007 behave differently.Markushttp://www.bemessung.comnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-37642350191085690652009-01-14T10:16:00.000-08:002009-01-14T10:16:00.000-08:00Here is working code with changes I made to work i...Here is working code with changes I made to work in Excel 2007 and include retrieving values of the filtered range.<BR/><BR/>Sub Make_Unique_List_InPlace()<BR/><BR/>Dim oWS As Worksheet ' Worksheet Object<BR/><BR/>Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique<BR/><BR/>On Error GoTo Disp_Error<BR/><BR/>' ---------------------------------------------<BR/><BR/>' Coded by Shasur for www.vbadud.blogspot.com<BR/><BR/>' ---------------------------------------------<BR/><BR/>'Demo1 is a defined name<BR/>Set oRange = Range("Demo1")<BR/><BR/><BR/>oRange.AdvancedFilter xlFilterInPlace, , , True<BR/><BR/><BR/>Dim ocell As CellFormat<BR/><BR/><BR/><BR/>If Not oRange Is Nothing Then<BR/><BR/>For Each Item In oRange<BR/> <BR/> MsgBox Item.Value<BR/> <BR/>Next<BR/><BR/>oRange = Nothing<BR/><BR/>End If<BR/><BR/><BR/>If Not oWS Is Nothing Then oWS = Nothing<BR/><BR/>' --------------------<BR/><BR/>' Error Handling<BR/><BR/>' --------------------<BR/><BR/>Disp_Error:<BR/><BR/>If Err <> 0 Then<BR/><BR/>MsgBox Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples"<BR/><BR/><BR/>Err.Clear<BR/><BR/><BR/><BR/>Resume Next<BR/><BR/>End If<BR/><BR/>End Sub<BR/><BR/>'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/rangeAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-91623580338099109622008-12-08T19:34:00.000-08:002008-12-08T19:34:00.000-08:00Thanks, It was because of the formatting I did it ...Thanks, It was because of the formatting I did it in VS2008 - was smart enough to remove 'Set'. I have corrected it nowM Shasurhttps://www.blogger.com/profile/17390752937266096344noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-5021753887767126002008-12-07T04:59:00.000-08:002008-12-07T04:59:00.000-08:00you have omitted the Set keyword in the following ...you have omitted the Set keyword in the following command:<BR/>oRange = Range("B:B")Anonymousnoreply@blogger.com