VBA Code To SORT Entire Sheet

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
109
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
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")
 

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
109
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
109
Fluff - That worked!!! Thanks so much for your FANTASTIC assistance!!!! You ROCK!!! Have a great weekend!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,680
Members
414,008
Latest member
SNesbyCarr

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
Top