Forum Discussion
replacing as variables in sort,subtotal macros
- I see it's in the previous routine, you therefore need to pass it along with the other variables to the SortSubtotal routine
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 Sub
You 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
pardon me, I do not see in this Message box the </> icon ..
where could it be? sorry for being so noob...
you mean I have to click this icon then type the codes for it to be inside a box?
man thanks
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
Mr. Hopkins
the one I'm having problem with is almost the same as the following SUBS, but they work!
for your reference.. many thanks
Sub ClearSheets()
Dim LastRow As Long
ClearSheet ("client")
ClearSheet ("item")
ClearSheet ("class")
ClearSheet ("status")
ClearSheet ("resolve")
End SubSub ClearSheet(msheet As Variant)
Sheets(msheet).Select
Cells.Select
Selection.RemoveSubtotal
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A2:J" & LastRow).Select
Selection.ClearContents
End Sub
