We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sort on column
Posted on 2016-07-23
I had this question after viewing sort on column.
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel
Whats happening
I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box. I have completed the sort by column (Sub SortDataByAcctCode()), but keep getting an error message when i sort by the ID (Sub SortID()) I always get an error message starting at ".AutoFilter.sort.SortFiel
Whats happening
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Your error is due to the fact that the autofiltering is not turned on when the macro hit this instruction:
.AutoFilter.sort.SortField
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
gowflow
.AutoFilter.sort.SortField
It is due to the fact that you are missing this instruction
.Range("A1:Z1").AutoFilter
My suggestion is that your macro should be like this:
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A1:Z1").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
gowflow
I have three Options Sort by Date Company and Account Code (see codes below) the above (gowflow) works when I select one option, but when i Bounce around from Company to Date to Account Code or any other combination I get an error code highlighting the
.AutoFilter.sort.SortFields.Clear
Sub SortDataByDate()
'
' SortDataByDate Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByCompany()
'
' SortDataByCompany Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Range("A1:Z1").AutoFilter
ActiveWindow.SmallScroll ToRight:=3
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A1:Z1").AutoFilter
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Sub SortID()
'
' SortID Macro
'
Application.ScreenUpdating = False
Sheets("Expenses").Visible = True
With Sheets("Expenses")
.Columns("A:A").Select
.Range("A:A").AutoFilter
.AutoFilter.sort.SortFields.Clear
.AutoFilter.sort.SortFields.Add Key:= _
.Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Sheets("Expenses").Visible = False
Application.ScreenUpdating = True
End Sub
Simply put this
.Range("A1:Z1").AutoFilter
After this line
ActiveWindow.SmallScroll ToRight:=3
gowflow
.Range("A1:Z1").AutoFilter
After this line
ActiveWindow.SmallScroll ToRight:=3
gowflow
Still not working
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
I have attached the workbook for your review
Select the Menu Tab
Click on the input Invoice Command Button
the Option Buttons are on the right above the list box
if you want to fix the error message every time i close the workbook, i wouldn't mind
Ops-GL7.4.xlsm
Featured Post
Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.