VBA按顏色對excel列進行排序

尋求幫助。我目前有一個帶有標題的作業表，隨著資料的加載，列和行繼續增加。我正在嘗試使用 VBA 按顏色排序。下面是我的第一遍，但由于 VBA 知識有限，它不起作用。任何幫助都會很棒。

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long

    Set wb = ActiveWorkbook

    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                For i = ws.Columns("A").Column To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                    With ws.Range(ws.Cells(1, i), ws.Cells(ws.Rows.Count, i).End(xlUp))
                        If .Cells.Count > 1 Then
                            .SortFields.Add(rng, xlSortOnCellColor, xlAscending, , xlSortNormal). _
                                SortOnValue.Color = RGB(255, 255, 208)
                            .SetRange rngSort
                            .Header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End If
                    End With
                Next i
        End Select
    Next ws

End Sub

uj5u.com熱心網友回復：

我對 Range.Sort 的理解是，您將無法到達您想要去的地方，而 SortField 的 Sort 物件正是顏色排序所需要的。

這是我對您的代碼的輕微修改：

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    Dim rng As Range    'Not absolutely necessary, but helpful to set a range object variable.

    Set wb = ActiveWorkbook

    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                For i = ws.Columns("A").Column To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                    
                    Set rng = ws.Range(ws.Cells(1, i), ws.Cells(ws.Rows.Count, i).End(xlUp))  
' Above, we set the range object variable _
' so we don't have to keep retyping that code.

                    With ws.Sort    'We are applying/creating the Sort object as a property of the worksheet, not the range.
                        If rng.Cells.Count > 1 Then
                            .SortFields.Clear  'Kill any previous sorting criteria and start fresh.
                            .SortFields.Add(Key:=rng, SortOn:=xlSortOnCellColor, _
                                Order:=xlAscending, DataOption:=xlSortNormal).SortOnValue.Color = RGB(255, 255, 208) 
'There's a lot happening in the line above.  We are both adding the new SortField object _
' (as a method of SortFields object -- notice the plural!), and we're setting the SortOnValue property. _
' For clarity, I think a better way to do that is to set another object variable there _
' and break those apart, but my goal is to stay close to your existing code with minimal change.
                            .SetRange rng
                            .Header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End If
                    End With
                Next i
        End Select
    Next ws

End Sub

如果目的是通過浮動到頂部的指示顏色一次一個地對每個單獨的列進行排序（這是您想要的嗎？），這對我有用。

我發現它可以幫助我設定更多的物件變數并使用它們來縮短 VBA 中的后續代碼，因此是我的“rng”物件。希望這能讓你到達你需要去的地方。

標籤：擅长 vba

上一篇：根據單個參考行的變化動態隱藏/取消隱藏一系列行

下一篇：自動過濾器不回傳任何行，因此可見范圍什么都不是，但過濾后的可見范圍不是什么