Sort columns in VBA

[Seanconn520]

I have a table of data that needs to be sorted within a macro.
the last row will be denoted by a counter and the first will be row number 4
I want it to sort the entire table by column B ascending
*
so basically my range will be ("A4" & ":" & "AA" & counter)
*
what I can´t figure out how to do is to have it sort by column b values

Any ideas? Thanks in advance

[Paul_Hossler]

I like to start by recording a macro if I'm not sure.

I can then clean it up by removing unneeded .Select, and other statments, as well as 'generalize' it


Option Explicit
Sub MacroAsResorded()
Range("A4:B25").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B5:B25") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A4:B25")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub



Sub MacroAsCleanedUp()
Dim counter As Long
Dim rDataToSort As Range, rSortData As Range

counter = 25

Set rSortData = Range("B5:B" & counter)
Set rDataToSort = Range("A4:B" & counter)

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:=rSortData, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rDataToSort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub



This seems to fit in with what you wanted to do, or at least will get you started

Paul

[mancubus]

thats not related with the subject but you can use:
("A4:AA" & counter)

rather than
("A4" & ":" & "AA" & counter)

[mancubus]

@ paul

i like to use 1-2 lines for sorting a table :)


LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("$A$4:$AA$" & LastRow)
SortRange.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlYes


or

Range("$A$4:$AA$" & Cells(Rows.Count, "A").End(xlUp).Row).Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes

[Paul_Hossler]

@mancubus -- you're right that less wordy is probably better, but if there is no performance impact, then my personal style / preferance / choice / opinion is to have my macros as self documenting (= wordy) as possible. Just my 2 cents

Paul