Vba sort columns in Excel

For example, I have Sales Data for Q1-2016 for different Salespersons as shown below. I would like to sort them in decreasing order of Sales using VBA.

excel vba sort columns

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub SortData()
  3. ActiveSheet.Sort.SortFields.Clear
  4. ActiveSheet.Sort.SortFields.Add Key:=Range("CY5:CY14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  5. With ActiveSheet.Sort
  6. .SetRange Range("CV4:CY14")
  7. .Header = xlYes
  8. .MatchCase = False
  9. .Orientation = xlTopToBottom
  10. .SortMethod = xlPinYin
  11. .Apply
  12. End With
  13. End Sub


a) Line 3 - Clear any existing sorting in ActiveSheet.

b) Line 4 - Define the Sort field (Sales value in Column CY) and Sort Order.

c) Line 6 - Define the Data range that needs to be sorted.

d) Line 7 - Since the Data range selected includes header, set ".Header" property to xlYes.

Result after Macro execution:

excel vba sort columns


You can find similar Excel Questions and Answer hereunder

1) How can I protect / unprotect WorkSheet using VBA?

2) How can I export a chart as a gif file?

3) How to hide and unhide rows and columns in Excel

4) How do I update my DropDown list whenever the sheet is activated?

5) Vba to return week numbers in Excel

6) How to delete rows with Excel VBA

7) I have a column header Title that is big - I cannot increase the size of column. In Excel, how can I somehow fit the Title retaining the existing column width?

8) How can I dynamically add series to an existing chart using VBA?

9) How can I get users to select a file for processing using my macro?

10) How can I find the last used cell in a Column in VBA?


Here the previous and next chapter