vba sort columns in Excel

Q158. In Excel, how can I sort data using VBA?

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

Description:

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 excel vba sort columns

 

You can find similar Excel Questions and Answer hereunder

1) Determine if hyperlinks are valid in Excel

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

3) How can I set FreezePanes in a certain range using VBA?

4) How can I add a legend to a chart using VBA?

5) Vba delete entire row if contains certain text in Excel

6) How can I delete all shapes in a WorkSheet?

7) How can I turn off Alerts using VBA?

8) How to change desktop background in Excel

9) How can I activate a routine when there is a change in value of a cell?

10) How do i put double quotes in a string in vba in Excel

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

12) How do I assign a macro to a shape using VBA?

13) How can I check if a file exists in a folder using VBA?

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

15) Userform initialize vs userform show in Excel