i have a a spreadsheet that users will constantly be adding new rows and deleting rows. I need to run some code that will sort the rows in descending order, then loop thru and evaluate each row.
So i scraped some code up that allows the user to specifiy how many rows there are.
Sub Holdings_Range() 'this allows the user to dynamically increase or decrease the range as Vouchers expand
NumVouchers = Cells(4, 10) 'cell J4 has number 12 in it
Set EndRange = Range("G" & 15 + NumVouchers)
Set StartRange = Range("A15")
Range(StartRange, EndRange).Select
End Sub
The above works great as the proper range is selected.
But now I have trouble passing that same range for sorting. I currently have it hard-coded with 15 and 27.
ActiveWorkbook.Worksheets("perf").Sort.SortFields.Clear
'*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
ActiveWorkbook.Worksheets("perf").Sort.SortFields.Add Key:=Range("D15:D27"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("perf").Sort
'*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
.SetRange Range("A15:G27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
And then I have trouble again passing the range to a loop which is also currently hard coded with 15 and 27.
For r = 15 To 27 Step 1
If Cells(r, 4).Value < Range("G10").Value Then
'Rows(r).ClearContents
Rows(r).Font.ColorIndex = 3 'red
End If
Next r
The sort and loops works fine hard-coded, but I just want to be able to pass the range from Holdings_Range() for the Sort and Loop.
So i scraped some code up that allows the user to specifiy how many rows there are.
Sub Holdings_Range() 'this allows the user to dynamically increase or decrease the range as Vouchers expand
NumVouchers = Cells(4, 10) 'cell J4 has number 12 in it
Set EndRange = Range("G" & 15 + NumVouchers)
Set StartRange = Range("A15")
Range(StartRange, EndRange).Select
End Sub
The above works great as the proper range is selected.
But now I have trouble passing that same range for sorting. I currently have it hard-coded with 15 and 27.
ActiveWorkbook.Worksheets("perf").Sort.SortFields.Clear
'*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
ActiveWorkbook.Worksheets("perf").Sort.SortFields.Add Key:=Range("D15:D27"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("perf").Sort
'*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
.SetRange Range("A15:G27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
And then I have trouble again passing the range to a loop which is also currently hard coded with 15 and 27.
For r = 15 To 27 Step 1
If Cells(r, 4).Value < Range("G10").Value Then
'Rows(r).ClearContents
Rows(r).Font.ColorIndex = 3 'red
End If
Next r
The sort and loops works fine hard-coded, but I just want to be able to pass the range from Holdings_Range() for the Sort and Loop.