Excel VBA Macro: How to sort using your selection rather than a specific range (e.g. A1-C100)

[ariesmethod]

I am trying to create a macro to catch all information for different time frames. For example, if there are 105 items, the macro below would only catch 100 of the items to sort. The selection for the macro does not have a specific range but the sort function does, which wouldn't make the macro work for all situations. Is there a way to make it based ONLY on selection?

'Sort all the information by description.
Sheets("ALL_DEDUCTIONS").Select
Cells.Find(What:="DED_CODE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort.SortFields.Add Key:=Range( _
"B2:B57"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort
.SetRange Range("A1:C100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Thanks in advance!

 

[JLGWhiz]

Without rewriting your code, try this:

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
[COLOR=#b22222]Dim addr As String
addr = Selection.Address
[/COLOR]ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort.SortFields.Add Key:=Range( _
"B2:B57"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ALL_DEDUCTIONS").Sort
.SetRange Range([COLOR=#b22222]addr[/COLOR])

the Dim statement can be moved to the top of the procedure just after the title line.

 

[ariesmethod]

Thank you very much kind sir! It worked perfectly! I still find it weird on how excel does not have that function when relative mode is on for recording macros.

Also, I tried putting the dim statement on the top of the procedure and it does not work for some reason. It gives an error on the ".apply" spot on the code i sent on the original message.

 

[JLGWhiz]

Thank you very much kind sir! It worked perfectly! I still find it weird on how excel does not have that function when relative mode is on for recording macros.

Also, I tried putting the dim statement on the top of the procedure and it does not work for some reason. It gives an error on the ".apply" spot on the code i sent on the original message.

The Dim Statement goes in the body of the Sub like:

Sub somethingorother()
Dim widgit As Variant
'Other code here
End Sub

It should not throw an error unless something is spelled incorrectly, duplicates something else or the data type is of unknown origin foreign to Excel. The Dim statement allows the compiler to reserve memory for the data type that is declared up front. It helps to speed up the run time a little bit and avoids VBA making the wrong decision on the data type if it is not declared.