Simplfying the Sort macro

  • Thread starter Thread starter ucanalways
  • Start date Start date
U

ucanalways

I would like to have a macro to sort every column in descending order.
Given below is the the sort macro I have for each and every column.
This implies I have to copy,paste and change the corresponding column
name 256 times.

Is there any simple way to do this? Thanks

Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Columns("B:B").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Columns("C:C").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Columns("D:D").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

etc till Column IV
 
Try

Sub SortEachColumn()
Dim LastCol As Long
Dim LastRow As Long
Dim N As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For N = 1 To LastCol
LastRow = Cells(Rows.Count, N).End(xlUp).Row
Range(Cells(1, N), Cells(LastRow, N)).Sort _
key1:=Cells(1, N), order1:=xlAscending
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Try

Sub SortEachColumn()
Dim LastCol As Long
Dim LastRow As Long
Dim N As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For N = 1 To LastCol
LastRow = Cells(Rows.Count, N).End(xlUp).Row
Range(Cells(1, N), Cells(LastRow, N)).Sort _
key1:=Cells(1, N), order1:=xlAscending
Next N
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)












- Show quoted text -
Click to expand...

Chip, your code works great. Thank you very much. Here is the code I
am using to suit my need

Sub sort_each_column()
Range("A1:IV65536").Value = ""
Sheets("Sheet1").Range("A1:IV65536").Copy
Sheets("Sheet4").Select
Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Dim LastCol As Long
Dim LastRow As Long
Dim N As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For N = 1 To LastCol
LastRow = Cells(Rows.Count, N).End(xlUp).Row
Range(Cells(1, N), Cells(LastRow, N)).Sort _
key1:=Cells(1, N), order1:=xlDescending
Next N

Range("A1").Select

End Sub
 
Back
Top