Modify Sort Routine to inlcude All Data

[JCO]

I have a sort routine shown below: However, I need H62, F62, B62 & I62 to
not be fixed. How can I determine this number which can be 3000 lines of
data. How do I enter this in the code.
Thanks

Private Sub SortCategoryAssistanceWithHeaders()
'///////////////////////////////////////////////////
'
' SortCategoryAssistanceWithHeaders Macro
' jco - This routine will do a triple level sort
' on 3-columns, omitting the Header
'
'///////////////////////////////////////////////////
'
Cells.Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key _
:=Range("H2:H62"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption _
:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key _
:=Range("F2:F62"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption _
:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key _
:=Range("B2:B62"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption _
:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:I62")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Claus Busch]

Hi,

Am Fri, 15 Nov 2013 14:02:20 -0600 schrieb JCO:

I have a sort routine shown below: However, I need H62, F62, B62 & I62 to
not be fixed. How can I determine this number which can be 3000 lines of
data. How do I enter this in the code.

try:
Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:I" & LRow).Sort key1:=.Range("H1"), _
order1:=xlAscending, key2:=.Range("F1"), _
order2:=xlAscending, key3:=.Range("B1"), _
order3:=xlAscending, Header:=xlYes
End With
End Sub


Regards
Claus B.

 

[JCO]

Ah! not sure I understand that but I will read about and learn. Thanks so
much, that is much simpler code. Most of my stuff comes from using the
recorder, then trying to understand it.'
Thanks again

"Claus Busch" wrote in message
Hi,

Am Fri, 15 Nov 2013 14:02:20 -0600 schrieb JCO:

I have a sort routine shown below: However, I need H62, F62, B62 & I62 to
not be fixed. How can I determine this number which can be 3000 lines of
data. How do I enter this in the code.

try:
Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:I" & LRow).Sort key1:=.Range("H1"), _
order1:=xlAscending, key2:=.Range("F1"), _
order2:=xlAscending, key3:=.Range("B1"), _
order3:=xlAscending, Header:=xlYes
End With
End Sub


Regards
Claus B.