无法对范围的自动筛选应用排序。

huangapple go评论85阅读模式
英文:

Cannot apply sorting to a range 's autofilter

问题

I have already defined the range and applied filter as follows.

  1. Dim wb As Workbook: Set wb = ThisWorkbook
  2. Set ws = wb.Sheets("Sheet1")
  3. With ws
  4. Dim tbl_rng As Range
  5. Set tbl_rng = .Range("$A$1:$H$1000")
  6. tbl_rng.AutoFilter ' Turn on autofilter
  7. tbl_rng.AutoFilter Field:=4, Criteria1:="=*AAA*", Operator:=xlAnd
  8. With tbl_rng.AutoFilter.Sort ' <- line of error
  9. .SortFields.Clear
  10. .SortFields.Add2 Key:=Range("B1:B1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  11. .Header = xlYes
  12. .MatchCase = False
  13. .Orientation = xlTopToBottom
  14. .SortMethod = xlPinYin
  15. .Apply
  16. End With
  17. End With
  18. Problem occurs with the `With tbl_rng.AutoFilter.Sort` statement; it complains `object required`. I tried `With .Sort`, it ran without error but it didn't sort the column.
  19. So what have I done wrong here? Why can I reference tbl_rng.Autofilter? Why is it not an object? How can I reference the autofilter of the range I defined instead of referencing the autofilter of the worksheet because I could have multiple autofilter tables on the same sheet?
英文:

I have already defined the range and applied filter as follow.

  1. Dim wb As Workbook: Set wb = ThisWorkbook
  2. Set ws = wb.Sheets(&quot;Sheet1&quot;)
  3. With ws
  4. Dim tbl_rng As Range
  5. Set tbl_rng = .Range(&quot;$A$1:$H$1000&quot;)
  6. tbl_rng.AutoFilter &#39; Turn on autofilter
  7. tbl_rng.AutoFilter Field:=4, Criteria1:=&quot;=*AAA*&quot;, Operator:=xlAnd
  8. With tbl_rng.AutoFilter.Sort &#39;&lt;- line of error
  9. .SortFields.Clear
  10. .SortFields.Add2 Key:=Range(&quot;B1:B1000&quot;), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  11. .Header = xlYes
  12. .MatchCase = False
  13. .Orientation = xlTopToBottom
  14. .SortMethod = xlPinYin
  15. .Apply
  16. End With
  17. End With

Problem occurs with the with tbl_rng.AutoFilter.Sort statement, it complains object required. I tried With .sort, it ran without error but it didn't sort the column.

So what have I done wrong here ? Why can I reference tbl_rng.Autofilter ?Why is it not an object ? How can I reference the autofilter of the range i defined instead of referencing the autofilter of the worksheet, because I could have multiple autofilter tables on the same sheet.

答案1

得分: 1

请尝试下一个适应的代码。首先应用 Sort,然后应用 AutoFilter

  1. Sub SortAutofilterRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
  4. Dim tbl_rng As Range
  5. With ws
  6. If .AutoFilterMode Then .AutoFilterMode = False
  7. Set tbl_rng = .Range("$A$1:$H$1000")
  8. With tbl_rng
  9. .Cells.Sort Key1:=.Columns(2), Order1:=xlAscending, _
  10. Orientation:=xlTopToBottom, Header:=xlYes
  11. .AutoFilter Field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
  12. End With
  13. End With
  14. End Sub

下一个版本首先对筛选的范围进行筛选,然后对其进行排序:

  1. Private Sub AutofilterSortRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
  4. Dim tbl_rng As Range
  5. With ws
  6. If .AutoFilterMode Then .AutoFilterMode = False
  7. Set tbl_rng = .Range("$A$1:$H$1000")
  8. With tbl_rng
  9. .AutoFilter Field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
  10. ws.AutoFilter.Range.Sort Key1:=.Columns(2), Order1:=xlAscending, _
  11. Orientation:=xlTopToBottom, Header:=xlYes '需要筛选AutoFilter.Range...
  12. End With
  13. End With
  14. End Sub

下一个版本能够对预先存在的筛选范围进行排序:

  1. Private Sub ExistingAutofilteredSortRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
  4. Dim tbl_rng As Range, autoFilt As Range
  5. With ws
  6. If Not .AutoFilterMode Then Exit Sub '没有应用任何筛选...
  7. Set tbl_rng = .Range("$A$1:$H$1000")
  8. With tbl_rng
  9. ' .AutoFilter Field:=4, Criteria1:="*AAA*", Operator:=xlFilterValues
  10. ws.AutoFilter.Range.Sort Key1:=.Columns(2), Order1:=xlAscending, _
  11. Orientation:=xlTopToBottom, Header:=xlYes
  12. End With
  13. End With
  14. End Sub
英文:

Please, try the next adapted code. It firstly apply Sort and then AutoFilter:

  1. Sub SortAutofilterRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets(&quot;Sheet1&quot;)
  4. Dim tbl_rng As Range
  5. With ws
  6. If .AutoFilterMode Then .AutoFilterMode = False
  7. Set tbl_rng = .Range(&quot;$A$1:$H$1000&quot;)
  8. With tbl_rng
  9. .cells.Sort key1:=.Columns(2), Order1:=xlAscending, _
  10. Orientation:=xlTopToBottom, Header:=xlYes
  11. .AutoFilter field:=4, Criteria1:=&quot;*AAA*&quot;, Operator:=xlFilterValues
  12. End With
  13. End With
  14. End Sub

Next version works firstly filtering and after that sorting the filtered range:

  1. Private Sub AutofilterSortRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets(&quot;Sheet1&quot;)
  4. Dim tbl_rng As Range
  5. With ws
  6. If .AutoFilterMode Then .AutoFilterMode = False
  7. Set tbl_rng = .Range(&quot;$A$1:$H$1000&quot;)
  8. With tbl_rng
  9. .AutoFilter field:=4, Criteria1:=&quot;*AAA*&quot;, Operator:=xlFilterValues
  10. ws.AutoFilter.Range.Sort key1:=.Columns(2), Order1:=xlAscending, _
  11. Orientation:=xlTopToBottom, Header:=xlYes &#39;need to filter the AutoFilter.Range...
  12. End With
  13. End With
  14. End Sub

And the next one is able to sort a pre existing filtered range:

  1. Private Sub ExistingAutofilteredSortRng()
  2. Dim wb As Workbook: Set wb = ThisWorkbook
  3. Dim ws As Worksheet: Set ws = wb.Sheets(&quot;Sheet1&quot;)
  4. Dim tbl_rng As Range, autoFilt As Range
  5. With ws
  6. If Not .AutoFilterMode Then Exit Sub &#39;no any filter applied...
  7. Set tbl_rng = .Range(&quot;$A$1:$H$1000&quot;)
  8. With tbl_rng
  9. &#39; .AutoFilter field:=4, Criteria1:=&quot;*AAA*&quot;, Operator:=xlFilterValues
  10. ws.AutoFilter.Range.Sort key1:=.Columns(2), Order1:=xlAscending, _
  11. Orientation:=xlTopToBottom, Header:=xlYes
  12. End With
  13. End With
  14. End Sub

huangapple
  • 本文由 发表于 2023年6月26日 14:39:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76554105.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定