筛选数据下按列N分组实现交替行颜色的VBA代码优化需求

问题根源 #

原代码遍历了N列所有行（含隐藏行），且直接用Cell.Offset(-1,0)获取上一行值——但筛选后隐藏行的存在会导致这个值并非可见的上一行的N列值，从而破坏颜色交替逻辑，出现同组颜色不统一、不同组颜色未切换的问题。

修正要点 #

• 仅遍历筛选后的可见行，用SpecialCells(xlCellTypeVisible)实现
• 新增变量记录上一个可见行的N列值，而非依赖Offset获取（避免指向隐藏行）
• 先清空所有可见行的底色，避免旧颜色干扰

修正后的完整代码 #

Sub DubbeleOrders()
    Dim Switch As Boolean
    Dim ws As Worksheet
    Dim Lastrow As Long
    Dim visibleRows As Range
    Dim cell As Range
    Dim prevNValue As Variant
    
    ' 创建新工作表并复制数据
    Sheets.Add(After:=Sheets("Page1")).Name = "dubbel"
    Set ws = Worksheets("dubbel")
    
    Sheets("Page1").Range("A9").CurrentRegion.Copy ws.Range("A1")
    ws.Columns("A:M").EntireColumn.AutoFit
    Application.CutCopyMode = False
    
    ' 设置辅助列标题和公式
    ws.Range("N1").Value = "art/qty/date"
    ws.Range("O1").Value = "controle"
    
    Lastrow = ws.Cells(Rows.Count, 2).End(xlUp).Row
    ws.Range("N2:N" & Lastrow).Formula = "=CONCATENATE(RC[-6]&"" "", ""QTY ""&RC[-4]&"" "",TEXT(RC[-2],""dd-mm-jj""))"
    ws.Range("O2:O" & Lastrow).Formula = "=IF(COUNTIF(R2C14:R" & Lastrow & "C14,RC[-1])=1,0,1)"
    
    ' 排序和筛选
    ws.Range("A1:O" & Lastrow).Sort Key1:=ws.Range("N1"), Header:=xlYes
    ws.Range("A1:O" & Lastrow).AutoFilter Field:=15, Criteria1:="1"
    
    ' 获取可见行（排除表头）
    On Error Resume Next ' 处理无可见行的情况
    Set visibleRows = ws.Range("N2:N" & Lastrow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleRows Is Nothing Then
        ' 先清空所有可见行的底色
        visibleRows.EntireRow.Interior.Pattern = xlNone
        Switch = False
        prevNValue = ""
        
        ' 遍历可见行
        For Each cell In visibleRows
            ' 对比当前可见行与上一个可见行的N列值，决定是否切换颜色
            If cell.Value <> prevNValue Then
                Switch = Not Switch
                prevNValue = cell.Value
            End If
            
            ' 应用颜色
            If Not Switch Then
                cell.EntireRow.Interior.Color = 14869218
            End If
        Next cell
    End If
End Sub

关键修改说明 #

1. 可见行筛选：用SpecialCells(xlCellTypeVisible)精准获取筛选后的可见行，避免遍历隐藏行
2. 上值跟踪：用prevNValue记录上一个可见行的N列值，确保颜色切换的判断基于连续的可见行数据
3. 底色重置：先清空可见行底色，避免之前的颜色残留影响新的上色逻辑
4. 边界处理：增加On Error Resume Next处理无可见行的异常情况，避免代码报错

内容的提问来源于stack exchange，提问作者shaye