ActiveSheet.AutoFilter.Sort.SortFields.Clear in Excel 2003

vba excel-2003
brWHigino picture brWHigino · Feb 26, 2014 · Viewed 42.3k times · Source

I have a macro that works in Excel 2013, but the following part of the code breaks when running the macro in Excel 2003:

Sheets("dados").Select
Range("A1").AutoFilter Field:=6, Criteria1:="<>"
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.AutoFilter.Sort.Apply

I wasn't able to find a clear reason why it is breaking. I read people mentioning the problem is the Sort object, but didn't find any replacement options. Is there a replacement for this filtering procedure which would work in Excel 2003?

I appreciate any help.

Answer

Dmitry Pavliv picture Dmitry Pavliv · Feb 26, 2014

Try this one:

With ThisWorkbook.Sheets("dados")
    .Range("A1").AutoFilter Field:=6, Criteria1:="<>"

    .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, DataOption1:=xlSortNormal
End With

Related questions

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row …

Read More
How to show current user name in a cell?

In most of the online resource I can find usually show me how to retrieve this information in VBA. Is there any direct way to get this information in a cell? For example as simple as =ENVIRON('User') (which did …

Read More
Get User Selected Range

How can I get a range of cells selected via user mouse input for further processing using VBA?

Read More