A Better Sort

B

BrianDP1977

I'm currently using the following code to do a sort of certain name
ranges on a sheet:


Code
-------------------
Private Sub CommandButton3_Click()
Dim Pass_Range As String, Pass_Start As String

Pass_Range = "Ext_DP6_P1"
Pass_Start = "Sort_DP6_P1_Start"
Call Module3.Sort_Data(Pass_Range, Pass_Start)

End Su
-------------------



Code
-------------------
Sub Sort_Data(nRange As String, SVar As String)

Range(nRange).Sort Key1:=Range(SVar), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Su
-------------------


However, this code is not great because it requires a start poin
variable to be passed to it (i.e. SVar). The problem that I'm runnin
into is that this named variable will get deleted if the row containin
this cell is deleted (which is possible considering how data is bein
entered and removed from the database). However, if I don't name th
cell, I run the chance that the Sort method will reference the wron
start point in the column being sorted if cells are inserted or delete
above this cell. Is there a better way to do a sort without having t
use the Sort method? I've seen other ways of doing this in the pas
(through For If statements and such but now I can't seem to find them)
Thanks
 
J

Jim Cone

B,

The Key arguments are used by Excel to determine the column to use
when sorting. Any cell in the sort range column or even the entire
sort column can be used for the Key.

Therefore the following would work...
Key1:=Range(nRange).Columns(3)
(the third column of the sort range, not
necessarily the spreadsheet 3rd column).
or
Key1:=Range(nRange)(1)
(uses the first cell in the sort range)

Jim Cone
San Francisco, USA
'---------------------------


"BrianDP1977"
wrote in message
I'm currently using the following code to do a sort of certain named
ranges on a sheet:
Code:
--------------------
Private Sub CommandButton3_Click()
Dim Pass_Range As String, Pass_Start As String
Pass_Range = "Ext_DP6_P1"
Pass_Start = "Sort_DP6_P1_Start"
Call Module3.Sort_Data(Pass_Range, Pass_Start)
End Sub
--------------------
Code:
--------------------
Sub Sort_Data(nRange As String, SVar As String)

Range(nRange).Sort Key1:=Range(SVar), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
--------------------

However, this code is not great because it requires a start point
variable to be passed to it (i.e. SVar). The problem that I'm running
into is that this named variable will get deleted if the row containing
this cell is deleted (which is possible considering how data is being
entered and removed from the database). However, if I don't name the
cell, I run the chance that the Sort method will reference the wrong
start point in the column being sorted if cells are inserted or deleted
above this cell. Is there a better way to do a sort without having to
use the Sort method? I've seen other ways of doing this in the past
(through For If statements and such but now I can't seem to find them).
Thanks.
BrianDP1977
 
Top