ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Multiple Sorts (https://www.excelbanter.com/excel-programming/299856-multiple-sorts.html)

Ruan[_3_]
Multiple Sorts
 
Hello,

Is there a better way to write the below code? I have 26 Columns of data,
which I want the data within each column to be sorted when the User clicks a
command button.


Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With ActiveSheet
.Range("B8:B37").Sort Key1:=.Range("B8:B37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("C8:C37").Sort Key1:=.Range("C8:C37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("D8:D37").Sort Key1:=.Range("D8:D37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("E8:E37").Sort Key1:=.Range("E8:E37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("F8:F37").Sort Key1:=.Range("F8:F37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End With
Range("B8").Select

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan

JE McGimpsey
Multiple Sorts
 
One way:

Public Sub Sort_CounselorNames(sht As Worksheet, pwd As String)
Application.ScreenUpdating = False
sht.Unprotect Password:=pwd
Dim i As Long
For i = 2 To 27
With Cells(8, i).Resize(30, 1)
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next i
Range("B8").Select
sht.Protect Password:=pwd
Application.ScreenUpdating = True
End Sub





In article ,
"Ruan" wrote:

Hello,

Is there a better way to write the below code? I have 26 Columns of data,
which I want the data within each column to be sorted when the User clicks a
command button.


Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With ActiveSheet
.Range("B8:B37").Sort Key1:=.Range("B8:B37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("C8:C37").Sort Key1:=.Range("C8:C37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("D8:D37").Sort Key1:=.Range("D8:D37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("E8:E37").Sort Key1:=.Range("E8:E37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("F8:F37").Sort Key1:=.Range("F8:F37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End With
Range("B8").Select

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan

Ruan[_3_]
Multiple Sorts
 
That works great, but for some reason it only sorts the Data after row 8.
What does "Resize" do?


"JE McGimpsey" wrote in message
...
One way:

Public Sub Sort_CounselorNames(sht As Worksheet, pwd As String)
Application.ScreenUpdating = False
sht.Unprotect Password:=pwd
Dim i As Long
For i = 2 To 27
With Cells(8, i).Resize(30, 1)
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next i
Range("B8").Select
sht.Protect Password:=pwd
Application.ScreenUpdating = True
End Sub





In article ,
"Ruan" wrote:

Hello,

Is there a better way to write the below code? I have 26 Columns of
data,
which I want the data within each column to be sorted when the User
clicks a
command button.


Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With ActiveSheet
.Range("B8:B37").Sort Key1:=.Range("B8:B37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("C8:C37").Sort Key1:=.Range("C8:C37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("D8:D37").Sort Key1:=.Range("D8:D37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("E8:E37").Sort Key1:=.Range("E8:E37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("F8:F37").Sort Key1:=.Range("F8:F37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End With
Range("B8").Select

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan

Ruan[_3_]
Multiple Sorts
 
Nevermind. I changed

Header:=xlGuess to Header:=xlNo

and now it works fine.

Thanks for your help
Ruan



"JE McGimpsey" wrote in message
...
One way:

Public Sub Sort_CounselorNames(sht As Worksheet, pwd As String)
Application.ScreenUpdating = False
sht.Unprotect Password:=pwd
Dim i As Long
For i = 2 To 27
With Cells(8, i).Resize(30, 1)
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next i
Range("B8").Select
sht.Protect Password:=pwd
Application.ScreenUpdating = True
End Sub





In article ,
"Ruan" wrote:

Hello,

Is there a better way to write the below code? I have 26 Columns of
data,
which I want the data within each column to be sorted when the User
clicks a
command button.


Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With ActiveSheet
.Range("B8:B37").Sort Key1:=.Range("B8:B37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("C8:C37").Sort Key1:=.Range("C8:C37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("D8:D37").Sort Key1:=.Range("D8:D37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("E8:E37").Sort Key1:=.Range("E8:E37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

.Range("F8:F37").Sort Key1:=.Range("F8:F37"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End With
Range("B8").Select

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan

Soo Cheon Jheong
Multiple Sorts
 
Ruan, try this:

Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

Application.ScreenUpdating = False
sht.Unprotect Password:=pwd

Dim CL As Range
For Each CL In Range("B8:AA30").Columns
CL.Sort Key1:=CL.Cells(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Next

sht.Protect Password:=pwd
Application.ScreenUpdating = True

End Sub


--
Soo Cheon Jheong
http://excel.hompy.com
Seoul, South Korea
_ _
^вп^
--



Soo Cheon Jheong
Multiple Sorts
 
Ruan, try this:

Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

Application.ScreenUpdating = False
sht.Unprotect Password:=pwd

Dim CL As Range
For Each CL In sht.Range("B8:AA30").Columns ' <== Changed
CL.Sort Key1:=CL.Cells(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Next

sht.Protect Password:=pwd
Application.ScreenUpdating = True

End Sub


--
Soo Cheon Jheong
http://excel.hompy.com
Seoul, South Korea
_ _
^вп^
--

"Soo Cheon Jheong" wrote in message
...
Ruan, try this:

Sub Sort_CounselorNames(sht As Worksheet, pwd As String)

Application.ScreenUpdating = False
sht.Unprotect Password:=pwd

Dim CL As Range
For Each CL In Range("B8:AA30").Columns
CL.Sort Key1:=CL.Cells(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Next

sht.Protect Password:=pwd
Application.ScreenUpdating = True

End Sub


--
Soo Cheon Jheong
http://excel.hompy.com
Seoul, South Korea
_ _
^вп^
--






All times are GMT +1. The time now is 06:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com