Modify Sort Routine to inlcude All Data
 

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

Modify Sort Routine to inlcude All Data
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Modify Sort Routine to include All Data
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2