VBA: Sort on two columns

[gio123bg]

Hi,
I have the necessity to execute a macro for sorting a list of data on two columns.
More exactly, let's suppose to have this situation:

A 10/2/2008 FFFF GGGG HHH
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
B 08/1/2008 HHHH SSSS IIIII
C 15/10/2008 AAA BBBB CCC

I need this sort:
A 10/2/2008 FFFF GGGG HHH
B 08/1/2008 HHHH SSSS IIIII
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
C 15/10/2008 AAA BBBB CCC

Any suggestion will be well appreciated.

Thanks in advance for your kind support.

Regars,

Giovanni

 

[onlyadrafter]

Hello,


try using the macro recorder.

 

[Andrew Poulsom]

If you record a macro while sorting by column A then by column C, doesn't that give you the code you want?

 

[gio123bg]

Hi,
as in my example, I need the necessity to sort both column 1 (A,B,C, ...) and column 2 (date grouped by sort in column 1) in my macro.

Any idea?
Thanks in advance.
Regards,

Giovanni

 

[Andrew Poulsom]

Like this?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 22/10/2008 by apoulsom
'
'
    Range("A1:E5").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub

 

[gio123bg]

Very good!
Thanks.

Regards,

Giovanni

 

[PacketGnome]

Hello,

I'm a little new at VBA programming but I've been learning a lot just over the past few days. Anyway, I have a spreadsheet with about 9000 rows, but this number may vary.

I need a macro to find the column that has the word "Date" in row 1, and then sort that column Newest to Oldest. Also, it needs to exclude row 1 from the sort because row 1 is my headers.

I used the macro recorder, the code results are below. But I need to tweak it a little bit. Thanks for your help!

Sub sort_by_date()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B17")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim c As Integer
    With ActiveSheet.Range("A1").CurrentRegion
        c = .Find(What:="Date", After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Column
            .Sort Key1:=.Cells(1, c), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

 

[PacketGnome]

Thanks. It looks like it's working. So this sorts every row in the spreadsheet by date, right? Just making sure.. There are columns to the right of the Date column, also. I just want to make sure that it sorts the entire spreadsheet, basically.

 

[Andrew Poulsom]

It sorts A1's current region by the column headed Date. The current region is the range surrounded by blank rows and columns.