AutoFilter.Sort sorting other columns?

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
32
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!


I'm trying to sort the column data of a sheet with VBA using AutoFilter, taking the worksheet in the exercise image.

When the data started at A1, the code I posted below the image sorted normally, in the order I wanted. But when I changed the base start to B2, I changed the code to suit, including the AutoFilter.Sort parameters.

And to my surprise, the columns are still sorted as if the initial column were column A. That is, if the code is configured to sort the data in column B, where the base starts, it is column C that is affected .

Could you help me understand why this happens, and how to solve it?


planilha1.PNG



VBA Code:
Sub F_Ribeiro

Dim wBase As Worksheet
Dim headerColumns As Range

Set wBase = Worksheets("Base")
Set headerColumns = Range(Cells(2, 2), Cells(2, Columns.Count).End(xlToLeft))

wBase.Activate

If wBase.AutoFilterMode = True Then
    wBase.AutoFilterMode = False
End If

With headerColumns.CurrentRegion
    .AutoFilter
    .Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, Key2:=.RangeI("C2"), Order2:=xlAscending
End With

End Sub


I'll be waiting. Thank you for your consideration. : - D
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,704
Office Version
  1. 365
Platform
  1. Windows
You are actually doing a Range sort, not an Autofilter sort, but the problem is that you are effectively offsetting your columns, try it like
VBA Code:
Sub F_Ribeiro()

Dim wBase As Worksheet
Dim headerColumns As Range

Set wBase = Worksheets("Base")
wBase.Activate

Set headerColumns = Range(wBase.Cells(2, 2), wBase.Cells(2, Columns.Count).End(xlToLeft))


If wBase.AutoFilterMode = True Then
    wBase.AutoFilterMode = False
End If

With headerColumns.CurrentRegion
    .AutoFilter
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes, Key2:=.Columns(2), Order2:=xlAscending
End With

End Sub
 
Solution

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
32
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You are actually doing a Range sort, not an Autofilter sort, but the problem is that you are effectively offsetting your columns, try it like
VBA Code:
Sub F_Ribeiro()

Dim wBase As Worksheet
Dim headerColumns As Range

Set wBase = Worksheets("Base")
wBase.Activate

Set headerColumns = Range(wBase.Cells(2, 2), wBase.Cells(2, Columns.Count).End(xlToLeft))


If wBase.AutoFilterMode = True Then
    wBase.AutoFilterMode = False
End If

With headerColumns.CurrentRegion
    .AutoFilter
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes, Key2:=.Columns(2), Order2:=xlAscending
End With

End Sub


Hi Fluff. I am so gratefull. I'll study why, but it worked!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,339
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As to the why, the dot before the word Range in this part:

Code:
Key1:=.Range("B2")

relates the range back to headerColumns.CurrentRegion If the top left cell of that current region is B2, you are effectively saying Range("B2").Range"B2"), which is actually a reference to C3.

Does that make sense?
 

Forum statistics

Threads
1,176,090
Messages
5,901,352
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top