Macro to sort data in descending order

Is it possible to sort two different ranges of data on seperate tabs in descending order using a macro?

I would like sort data in both tabs by order of volume while keeping all data in tact. Also I do not want to include the header or "total" row in the sort.

Please refer to the .xls file for reference.

Thanks!

Hi Vbort44,

As long as the data consistently starts at cell A2 for both Sheets and the Volume Columns for Sheet1 and Sheet2 are B and D respectively, the following will do the job:

Sub Macro1()

Dim lngLastRow As Long
Dim intLastColumn As Integer
Dim strActiveTab, strActiveCell, strLastColumn As String

'Set the 'strActiveTab' and 'strActiveCell' variables as these will _
be used to return the user back to upon completion.
strActiveTab = ActiveSheet.Name
strActiveCell = ActiveCell.Address(False, False)

'As 'Sheet1' here refers to the first sheet in the workbook (not the _
tab called 'Sheet1'), you can rename the tab want you want and the code _
will work fine so long as it stays as the first (furthest left) tab _
of the workbook.
With Sheet1
    .Select
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    strLastColumn = ColumnLetter(intLastColumn)
    .Range("A2:" & strLastColumn & lngLastRow - 1).Sort _
        Key1:=Range("B2"), Order1:=xlDescending, Header:=xlNo
End With

'As 'Sheet2' here refers to the second sheet in the workbook (not the _
tab called 'Sheet2'), you can rename the tab want you want and the code _
will work fine so long as it stays as the second (to the immediate right _
of 'Sheet1' above) tab of the workbook.
With Sheet2
    .Select
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    strLastColumn = ColumnLetter(intLastColumn)
    .Range("A2:" & strLastColumn & lngLastRow - 1).Sort _
        Key1:=Range("D2"), Order1:=xlDescending, Header:=xlNo
End With

'Return to the point where the macro commenced.
Sheets(strActiveTab).Select
Range(strActiveCell).Select

MsgBox "The data has now been sorted in descending order by volumn.", vbInformation, "Data Sort Editor"

End Sub
'http://www.freevbcode.com/ShowCode.asp?ID=4303
Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

HTH

Robert

Originally Posted by Trebor76

Hi Vbort44,

As long as the data consistently starts at cell A2 for both Sheets and the Volume Columns for Sheet1 and Sheet2 are B and D respectively, the following will do the job:

Sub Macro1()

Dim lngLastRow As Long
Dim intLastColumn As Integer
Dim strActiveTab, strActiveCell, strLastColumn As String

'Set the 'strActiveTab' and 'strActiveCell' variables as these will _
be used to return the user back to upon completion.
strActiveTab = ActiveSheet.Name
strActiveCell = ActiveCell.Address(False, False)

'As 'Sheet1' here refers to the first sheet in the workbook (not the _
tab called 'Sheet1'), you can rename the tab want you want and the code _
will work fine so long as it stays as the first (furthest left) tab _
of the workbook.
With Sheet1
    .Select
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    strLastColumn = ColumnLetter(intLastColumn)
    .Range("A2:" & strLastColumn & lngLastRow - 1).Sort _
        Key1:=Range("B2"), Order1:=xlDescending, Header:=xlNo
End With

'As 'Sheet2' here refers to the second sheet in the workbook (not the _
tab called 'Sheet2'), you can rename the tab want you want and the code _
will work fine so long as it stays as the second (to the immediate right _
of 'Sheet1' above) tab of the workbook.
With Sheet2
    .Select
    lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    strLastColumn = ColumnLetter(intLastColumn)
    .Range("A2:" & strLastColumn & lngLastRow - 1).Sort _
        Key1:=Range("D2"), Order1:=xlDescending, Header:=xlNo
End With

'Return to the point where the macro commenced.
Sheets(strActiveTab).Select
Range(strActiveCell).Select

MsgBox "The data has now been sorted in descending order by volumn.", vbInformation, "Data Sort Editor"

End Sub
'http://www.freevbcode.com/ShowCode.asp?ID=4303
Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

HTH

Robert

Perfect! Many thanks!