ExcelMadeEasy: Vba sort columns in Excel

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:

Option Explicit
Sub SortData()
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("CY5:CY14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("CV4:CY14")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
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 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