Forum Discussion

Mark Lee's avatar
Mark Lee
Copper Contributor
Sep 17, 2018
Solved

vba coding errors

Hi,   i have a spreadsheet which is created using macros to filter and remove unnecesary data. This is uploaded for others to use but i need to use this base document to issue copy out some specifi...
excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Sep 17, 2018

    The code fails because the range addresses are wrong. Remove all dollar signs at the end of the addresses, example:

     

    change:

    Sheets("Department").Range("$A$1:$AJ$1500$").AutoFilter Field:=1, Criteria1:= _
    "<>*DVA*", Operator:=xlAnd

    to:

    Sheets("Department").Range("$A$1:$AJ$1500").AutoFilter Field:=1, Criteria1:= _
    "<>*DVA*", Operator:=xlAnd

Karl STEWING's avatar
Karl STEWING
Copper Contributor
Sep 17, 2018

Hello Mark -

 

For the first ones, it seems that is a typo

ActiveSheet.Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= "<>*DVA*", Operator:=xlAnd

Should rather be $AJ$1500 ?

 

There also a typo in this one: ActiveSheets.AutoFilter Field:=1, Criteria1:= "<>*DVA*", Operator:=xlAnd

The extra s shouldn't be there.

 

And for the last one, you'll need a Range, I don't think you can Apply the AutoFilter command to an entire spreadsheet.

 

Hope this helps.

- Karl

 

Resources