AutoFilter.Sort sorting other columns?

[F_Ribeiro]

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?

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

 

[Fluff]

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

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

 

[F_Ribeiro]

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

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]

As to the why, the dot before the word Range in this part:

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?