Macro Help

[JasonP CCTM LV]

I create lease payment schedules for different cities. The schedules have 9
columns across, but are varying in the number of rows down, anywhere from 30
to 3000+.

I would like to make a macro that would automatically sort by :

1. if the Payee (who the payment is to) has one line or more than one
(meaning if he is receiving 2 or more payments, move to the bottom), then
2. by the Payee Name, Alice Smith with 4 lines before John Smith with 2 lines
3. then by the payment number (alice smith has 4 payment numbers, john smith
has 2)

I can figure out the macro, but the problem is since the number of rows vary
by schedule and sometimes by month, (New York usually has about 3000, but one
month might be 2500, etc), I cant use the same macro because the cursor
always moves down a specific number of rows.

For Example: If I create the macro using a table of only 300 rows, but then
try to use it on a table of 3000, the macro will only format the first 300
rows.

Can anyone figure out how to make the cursor go to the bottom of the table
instead of the specific number of rows?

Below is the macro:

Sub FleetSort()
'
' FleetSort Macro
'

'
Columns("D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D5").Select
ActiveCell.FormulaR1C1 = "=OR(RC[-1]=R[-1]C[-1],RC[-1]=R[1]C[-1])"
Range("C6").Select
Selection.End(xlDown).Select
Range("D101").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Columns("D").ColumnWidth = 20.86
Columns("D").EntireColumn.AutoFit
Range("D8").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("D5101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("C5:C101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("H5:H101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("G5:G101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("B5:B101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B4:I101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-24
End Sub

 

[bpeltzer]

It looks like you've already got a line in the macro to move to the bottom...
'Selection.End(xlDown).Select'. Now you just need to record the row it
moved to and use that instead of the hard-coded value of 101.
Before the first line, Columns("D").Select, add a line to create the
variable:
Dim FinalRow as Long
Then after the line Selection.End(xlDown).Select, add a line to save the
ending row:
FinalRow = Selection.Row
Finally, replace all the ranges that specify row 101 using the new variable.
For example, instead of Range("D5101") you'd use Range("D5" & FinalRow)

I create lease payment schedules for different cities. The schedules have 9
columns across, but are varying in the number of rows down, anywhere from 30
to 3000+.

I would like to make a macro that would automatically sort by :

1. if the Payee (who the payment is to) has one line or more than one
(meaning if he is receiving 2 or more payments, move to the bottom), then
2. by the Payee Name, Alice Smith with 4 lines before John Smith with 2 lines
3. then by the payment number (alice smith has 4 payment numbers, john smith
has 2)

I can figure out the macro, but the problem is since the number of rows vary
by schedule and sometimes by month, (New York usually has about 3000, but one
month might be 2500, etc), I cant use the same macro because the cursor
always moves down a specific number of rows.

For Example: If I create the macro using a table of only 300 rows, but then
try to use it on a table of 3000, the macro will only format the first 300
rows.

Can anyone figure out how to make the cursor go to the bottom of the table
instead of the specific number of rows?

Below is the macro:

Sub FleetSort()
'
' FleetSort Macro
'

'
Columns("D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D5").Select
ActiveCell.FormulaR1C1 = "=OR(RC[-1]=R[-1]C[-1],RC[-1]=R[1]C[-1])"
Range("C6").Select
Selection.End(xlDown).Select
Range("D101").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Columns("D").ColumnWidth = 20.86
Columns("D").EntireColumn.AutoFit
Range("D8").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("D5101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("C5:C101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("H5:H101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("G5:G101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("B5:B101") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B4:I101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-24
End Sub

 

[JasonP CCTM LV]

The "101" referenced in the macro comes from the fact that the table I
created the macro with had 101 rows.

So all I need to do is replace that "101" with "& FinalRow" ?

 

[bpeltzer]

Yes, but only after you've captured the value of FinalRow with the assignment
FinalRow = Selection.Row following the xlDown. And be careful with the
quotes... In the original code, a line like "D5101" has quotes to indicate
a string. You'd replace that with "D5:" & FinalRow. You still need to get
the quote AFTER the string literal (D5, or you'll wind up with compile
errors.