Forum Discussion
replacing as variables in sort,subtotal macros
In the macro for Sorting (below), I wish to replace "C2:C" with a variable (mrng), I wonder what is the right DIM - as variant? string?
also, In the macro for SubTotal (below), I wish to replace the number 3 of GroupBy:= with a variable (mcol), what is the right DIM - as long? string? variant?
I understand 3 stands for column 3
many many thanks
Sort macro:
ActiveWorkbook.Worksheets("client").Sort.SortFields.Add Key:=Range("C2:C" & LastRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
with variable mrng - is below correct?
ActiveWorkbook.Worksheets("client").Sort.SortFields.Add Key:=Range(mrng & LastRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
SubTotal macro:
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
with variable mcol - is below correct?
Selection.Subtotal GroupBy:=mcol, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
- I see it's in the previous routine, you therefore need to pass it along with the other variables to the SortSubtotal routine
28 Replies
Hi Lorenzo
I'd recommend defining a range name for the first cell you want to sort and resizing the range based on the Integer Last Cell
e.g I named cell C2 "SortRef"
Avoiding hard-coding cell references and sheet names in your code is the key to preventing it from breaking easily as people insert/delete rows and columns or sheets are renamed.
Sub Sort() Dim LastRow As Integer Dim rngSort As Range LastRow = 20 Set rngSort = Range("SortRef").Resize(LastRow, 1) rngSort.Sort rngSort Set rngSort = Nothing End SubYou should avoid referencing sheet names in your code. e.g. "client"
Best to refer to range names or sheet numbers e.g. Sheet1.Activate
The 3, could be set as an Integer which is any number –32,768 to 32,767
BTW
how do you put the codes inside a 'box' like the one you did for Sub Sort()
thanks
Did you change mrng to a string?
to enter code, click in the reply button and then there is a </> icon
Mr Hopkins
Thank you for your reply and advice.
the macros are already in place and working, I just wanted to trim down the codes to make it more modular.
I placed (SUB below) in a module, then I call it in another by:
SortSubtotal("client","C2:C",3) ;
there is compile error := expected and I think the error is in the 3,
could you help me straigthen this out.
many many thanks
Sub SortSubtotal(msheet As Variant, mrng As Range, mcol As Integer)
Sheets(msheet).Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets(msheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(msheet).Sort.SortFields.Add Key:=Range(mrng & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(msheet).Sort
.SetRange Range("A1:J" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:J" & LastRow).Select
Selection.Subtotal GroupBy:=mcol, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Subthe mcol above may be the culprit..
I think both msheet and mrng should be String variables.
While your code is working now you should make the effort to make it as "bullet proof" as possible.
Just friendly advice as referencing Range("A1:J" & LastRow) can cause heartache further down the line.
Out of interest what is the significance of the prefix m you are using?
Can you post the message that appears when you run the Debugging Compile process
Also, stepping through your code using the F8 key will show you which step causes the issue
