Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Multiple Sorts
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
_ _
^ąŻ^
--


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default 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
_ _
^ąŻ^
--




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum by different sorts livetohike Excel Discussion (Misc queries) 2 November 5th 07 02:41 PM
Sorts Mona Marie Excel Worksheet Functions 3 July 21st 06 06:07 PM
same range, multiple sheets, different sorts, help please! nikmasteed Excel Worksheet Functions 2 May 19th 06 04:30 PM
Excel gets subtotals out of order using multiple sorts and subtot. jeffl Excel Discussion (Misc queries) 1 March 29th 05 01:35 AM
Macro for Multiple Sorts Dakota Excel Programming 5 February 4th 04 03:25 PM


All times are GMT +1. The time now is 02:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement