VBA Code To SORT Entire Sheet

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
150
Office Version
  1. 365
Platform
  1. Windows
I used Macro recording to get the code to sort a large sheet that I sort often. I used it for a while and it worked just fine. Unfortunately, I rearranged the sheet, ran the macro and becuase of the fixed ranges in the macro, it sorted one part of the sheet but not the other cells so now the values in one column are associated with the incorrect rows.

To prevent this from happening again, I want to set the entire sheet as the range. For the life of me, and after several days off and on of messing with it, I can't figure out to do this. Below is the base code and where I am having difficulty.

Any help would be greatly appreciated!!!!

Current Macro
Sub Sort_Master_To_Do_List()
'
' set active cell to recall at end
strCellNow = ActiveCell.Address

' initilize sort
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Clear

' SORT FIELD: Close Date
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("I2:I5000"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
DataOption:=xlSortNormal

' SORT FIELD: Pri
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A2:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal

' SORT FIELD: Area
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("E2:E5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal

' SORT FIELD: Description
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("F2:F5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal

' execute sort
With ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort
.SetRange Range.Cells.Select <<<<<<<<<<<---- this ie the code line that does not work. I have done lots of variations on this with no luck
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' select all cells and autufit row height
Cells.Select
Selection.Rows.AutoFit

' restore active cell before sorting was done
Range(strCellNow).Select

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
When using autofilter sort you do not specify the SetRange as it applies to the autofilter range.
Try just removing that line, but make sure that the autofilter is set for your entire data.
 
Upvote 0
Forgot to mention, you should also the the ranges in the keys to be just the row that contains the autofilter.
So if the autofilter is on row 1 use Range("I1")
 
Upvote 0
Fluff -
Thanks for the feedback and assistance!!!! Now that I understand autofilter is likely what caused my issue with getting my rows mixed up because I forgot to autofilter the columns I added and then ran the macro, I now want to remove the autofilter. I did the changes I thought were necessary but still can’t sort - need two bits of assistance:
• That one line of code as noted in the modified code below - in italics/bold/underline
• Are there any other modifications that need to be made to this code?
THANKS!!!!!
---------------------------------------------------------------------------------------------------------------------
Rich (BB code):
Sub TEST_Sort_Master_To_Do_List()
'
' set active cell to recall at end
    strCellNow = ActiveCell.Address

' initilize sort
    ActiveWorkbook.Worksheets("MASTER-test").Sort.SortFields.Clear

' SORT FIELD: Close Date
    ActiveWorkbook.Worksheets("MASTER-test").Sort.SortFields.Add2 Key:= _
        Range("I2"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
        DataOption:=xlSortNormal

' SORT FIELD: Pri
    ActiveWorkbook.Worksheets("MASTER-test").Sort.SortFields.Add2 Key:= _
        Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal

' SORT FIELD: Area
    ActiveWorkbook.Worksheets("MASTER-test").Sort.SortFields.Add2 Key:= _
        Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal

' SORT FIELD: Description
    ActiveWorkbook.Worksheets("MASTER-test").Sort.SortFields.Add2 Key:= _
        Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal

' execute sort
    With ActiveWorkbook.Worksheets("MASTER-test").Sort
        .SetRange Range.Cells.Select or .SetRange .Cells(1, 1).CurrentRegion <<<------ not sure how to set the range here to all of the cells in the table to be sorted
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' select all cells and autufit row height
    Cells.Select
    Selection.Rows.AutoFit

' restore active cell before sorting was done
    Range(strCellNow).Select

End Sub
 
Last edited by a moderator:
Upvote 0
Try it like
VBA Code:
Sub TEST_Sort_Master_To_Do_List()
'
' set active cell to recall at end
    strCellNow = ActiveCell.Address

' initilize sort
    
   With ActiveWorkbook.Worksheets("Master-test").Sort
      .SortFields.Clear

' SORT FIELD: Close Date
      .SortFields.Add2 Key:=Range("I2"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
         DataOption:=xlSortNormal

' SORT FIELD: Pri
      .SortFields.Add2 Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
         :=xlSortNormal

' SORT FIELD: Area
      .SortFields.Add2 Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
         :=xlSortNormal

' SORT FIELD: Description
      .SortFields.Add2 Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
         :=xlSortNormal

' execute sort
    
        .SetRange .Parent.UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' select all cells and autufit row height
    Cells.Select
    Selection.Rows.AutoFit

' restore active cell before sorting was done
    Range(strCellNow).Select

End Sub
 
Upvote 0
Solution
Fluff - That worked!!! Thanks so much for your FANTASTIC assistance!!!! You ROCK!!! Have a great weekend!!!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top