Sort Routine Macro for Excel Worksheet

I have an excel that requires a sort routine.  I tried using the start/stop 
recording.  The code is shown below, however, I need this routine to work 
when you don't know how many rows are in the data.  My method has the 
filename hardcoded too.  I don't know if that matters.  This one subroutine 
of many.  Can someone show me how to make this routine more dynamic.  It 
will always sort by the same Column but it needs to work with all different 
Ranges of data (this example is hardcoded to sort data from row 2 through 
row 613.

Sub SortCategoryAssistance()
    Cells.Select
    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Add 
Key:= _
        Range("P2:P613"), SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort
        .SetRange Range("A1:T613")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub
 

0
JCO
6/7/2010 5:31:30 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2377 Views

Similar Articles

[PageSpeed] 23

never mind.  I figured it out as shown below:
'
' SortCategoryAssistanceWithHeadersMacro
'
'
    Cells.Sort Key1:=Range("P2"), _
    Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    '
    Range("A17").Select


"JCO" <someone@somewhere.com> wrote in message 
news:OHGNSHoBLHA.5584@TK2MSFTNGP06.phx.gbl...
> I have an excel that requires a sort routine.  I tried using the 
> start/stop recording.  The code is shown below, however, I need this 
> routine to work when you don't know how many rows are in the data.  My 
> method has the filename hardcoded too.  I don't know if that matters. 
> This one subroutine of many.  Can someone show me how to make this routine 
> more dynamic.  It will always sort by the same Column but it needs to work 
> with all different Ranges of data (this example is hardcoded to sort data 
> from row 2 through row 613.
>
> Sub SortCategoryAssistance()
>    Cells.Select
>    ActiveWorkbook.Worksheets("NEEDWest Error 
> Report").Sort.SortFields.Clear
>    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Add 
> Key:= _
>        Range("P2:P613"), SortOn:=xlSortOnValues, Order:=xlAscending, 
> DataOption _
>        :=xlSortNormal
>    With ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort
>        .SetRange Range("A1:T613")
>        .Header = xlYes
>        .MatchCase = False
>        .Orientation = xlTopToBottom
>        .SortMethod = xlPinYin
>        .Apply
>    End With
>    Range("A1").Select
> End Sub
>
> 
0
JCO
6/7/2010 9:25:06 PM
Reply:

Similar Artilces: