VBA Case Insensitive Advanced Filter Sort

I have coded a macro to remove duplicates from a list of data - I now need to modify this code so that it is case insensitive (e.g. so duplicate records that are spelt the same are removed, regardless of capitalisation).

For example, if I had a list of the following:

BALL
Ball
Cat 
CAT
Dog 

I would like the macro to output:

Ball
Cat 
Dog

Here is the code I have at the moment - any help will be appreciated (please note that I have tried inserting the "Option Compare Text" command at the start of the module):

Option Compare Text

Sub Delete_Duplicates()

Application.ScreenUpdating = False
Worksheets("Proposal Database").Activate

With ActiveSheet

.Range("B2", .Range("B2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A2"), Unique:=True

End With

Worksheets("Input Screen").Activate
Application.ScreenUpdating = True

End Sub

Answers


Use the excel-built-in function (which is case insensitive) like this:

Sub Delete_Duplicates()

    Application.ScreenUpdating = False
    Worksheets("Proposal Database").Activate

    ActiveSheet.Columns(2).RemoveDuplicates

    Worksheets("Input Screen").Activate
    Application.ScreenUpdating = True

End Sub

Need Your Help

Managing User Profiles with PHP/MySQL for Beginners

php mysql user-profile

I am beginner in PHP/MySql and would develop a simple site that has user management. I like the idea of using OpenId's (like stackoverflow uses).

Session Helper Methods in Michael Hartl Tutorial Chapter 8

ruby-on-rails ruby ruby-on-rails-3 ruby-on-rails-4

I'm going through Michael Hartl tutorial on Ruby on Rails and I'm having trouble understanding some logic. Note that the logic works, it's just not resonating with me on what's actually happening....