Jump to content

Sorting an Excel worksheet using VBScript

LawrenceMW's Photo
Posted Dec 12 2009 11:53 PM
9506 Views

How can I use VBScript to sort an Excel worksheet in descending order? I can sort in ascending order because that is the default using range.Sort, but using the Excel constant xlDescending to sort in descending order results in an error.

Tags:
0 Subscribe


2 Replies

0
  CurtFrye's Photo
Posted Dec 19 2009 05:06 PM

Hi Lawrence,

If you can't get your VBA to work, I recommend recording a macro, sorting the range in descending order, and using that code. I put data in the range A1:A5 and recorded this macro:

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A5")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
0
  mevelyn's Photo
Posted Oct 17 2011 06:11 PM

Have you ever considered sorting excel worksheets by using an available tool? Kutools for Excel is just that kind of application helping you with most of the frastrating work in Excel. It provides a function to sort multiple workbook with alphabetical or numeric order in one click. To get descending order is also very simple. The add-ins collection do save much time in your work.