按字母排序和单元格颜色排序 [英] Sorting Alphabetically and by Cell Color

问题描述

我正在尝试按字母顺序排列多个列表，并按单元格颜色排序，但在一定范围内（因此不需要太长时间）。基本上，VBA假设选择列B行3-88，按字母排序，然后按颜色排序。然后移动到下一列C3：C88等，直到列NY。

I'm trying to sort multiple lists Alphabetically and by Cell Color but within a certain range (so it doesn't take too long). Basically the VBA is suppose to select for example column B rows 3-88, sort by alphabet, then sort by color. Then move to next column C3:C88, etc, until column NY.

当我尝试它，我得到运行时错误1004：对象_Global的方法范围失败。

When I try it, I get Run-time error 1004: Mathod "Range" of object '_Global' failed.

这是我的VBA：

Sub SortAlphaColor()
' Sorts rows within a list from A-Z
' Run Clean all first to avoid sorting blanks
' Set maximum range to avoid sorting too many rows

    Dim rngFirstRow As Range
    Dim rng As Range
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set rngFirstRow = ws.Range("B3:NY3")
    For Each rng In rngFirstRow
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rng, Order:=xlAscending
            'assuming there are no blank cells..
            .SetRange ws.Range(rng, rng.Range("B88").End(xlUp))
                'VBA from second module
                .SortFields.Add(Range(rng), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206) <- this line is highlighted in debug
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    Next rng
    Application.ScreenUpdating = True
End Sub

推荐答案

对我来说：

Sub SortAlphaColor()

    Dim rngFirstRow As Range
    Dim rng As Range, rngSort As Range
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set rngFirstRow = ws.Range("B3:NY3")
    For Each rng In rngFirstRow.Cells
        With ws.Sort

            Set rngSort = rng.Resize(86, 1) 'to row 88

            .SortFields.Clear
            .SortFields.Add Key:=rng, SortOn:=xlSortOnValues, _
                            Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add(rng, xlSortOnCellColor, xlAscending, , xlSortNormal). _
                            SortOnValue.Color = RGB(198, 239, 206)
            .SetRange rngSort
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply

        End With
    Next rng
    Application.ScreenUpdating = True
End Sub

这篇关于按字母排序和单元格颜色排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！