VBA, AutoFilters and the Condition

[Hartke]

I'm a student in the current VBA class and I thought I'd get my hands dirty writing some useful code. I have a data sheet that contains lots of sales information that I want to share with other divisions. However, our security mandates that we never share information that is not associated with that division - for example, people in Atlanta never see Los Angeles' data or vice versa.


I know how to do this using a row-by-row condition, but this is slow and I thought I might gain some advantages using AutoFilter.


To recap: What I want to do is delete all information on the sheet that does not contain the division name. The division name is input in an inputBox and stored as the variable strName.

Sheet2.Activate

ActiveSheet.Range("$A$5:$K$25970").AutoFilter Field:=5, Criteria1:=<>"strName", Operator:=xlFilterValues 'selects everything but intended branch

Set Start = Range("6:6")

Set Finish = Start.End(xlDown)

Set DeleteRange = Range(Start, Finish)


        DeleteRange.Clear

The line beginning with ActiveSheet returns a syntax error. What am I doing wrong?


PS Please feel free to suggest a better way to solve my problem

 

[Hui]

Hartke


Try the following small mod:

[pre]

Dim myCriteria as String
Sheet2.Activate
myCriteria = "<>" & strName
ActiveSheet.Range("$A$5:$K$25970").AutoFilter Field:=5, Criteria1:=myCriteria, Operator:=xlFilterValues 'selects everything but intended branch

[/pre]

 

[Hartke]

Thank you, Hui, that worked like a charm.


I'll remember to try this method when strings get a little sticky.