VBA, coding a dynamic range (column) for sorting data

Question

I am a bit of a rookie in vba and I am stuck on something which should be relatively easy: I have a marco specifying the column number from the headers names:

Dim onomata As Integer
'find column named Name of ship
Set acell = rows(1).Find(What:="Name of ship", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not acell Is Nothing Then
    onomata = acell.Column
End If

Now I want to sort my data based on this column:

Cells.Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( *this is where I want to introduce the column* ), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A1:AR100000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

However, my variable is integer while the command requests a range value. Can anyone help me in how to code it?

Subodh Tiwari sktneer · Accepted Answer · 2017-08-11 12:45:22Z

1

You only need this...

Dim acell As Range

'find column named Name of ship
Set acell = Rows(1).Find(What:="Name of ship", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not acell Is Nothing Then        
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Range("A1").Sort key1:=acell.Offset(1, 0), order1:=xlAscending, Header:=xlYes
End If

edited Aug 11, 2017 at 12:45

answered Aug 11, 2017 at 12:25

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Subodh Tiwari sktneer Over a year ago

@ShaiRado Yes that doesn't make any sense. I didn't look at it closely and copied OP's code. Lol Thanks for pointing that out. :)

Shai Rado · Accepted Answer · 2017-08-11 12:25:46Z

0

Stating ActiveSheet is enough, there's no meaning to using ActiveWorkbook.ActiveSheet.

Use the code below to sort according to the numeric result you got for onomata.

With ActiveSheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Columns(onomata), SortOn:=xlSortOnValues, _
                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
End With

answered Aug 11, 2017 at 12:25

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

2 Comments

e-gnacio Over a year ago

What if I want to sort by more than 1 criterion? like first sort by column A and then by column B???

Shai Rado Over a year ago

@e-gnacio sounds like a great new SO post, you should open a new one, this will allow other users (and me) to assist you

Rob Anthony · Accepted Answer · 2017-08-11 16:02:48Z

0

ActiveSheet.Sort.SortFields.Add Key:= ActiveSheet.Cells(onomata).EntireColumn

edited Aug 11, 2017 at 16:02

answered Aug 11, 2017 at 12:20

Rob Anthony

1,8431 gold badge17 silver badges20 bronze badges

Collectives™ on Stack Overflow

VBA, coding a dynamic range (column) for sorting data

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest