如何修改VBA筛选宏的目标列，将原筛选F列改为B列？

修复后完整代码 #

Function GetWorksheet(shtName As String) As Worksheet
    On Error Resume Next
    Set GetWorksheet = Worksheets(shtName)
End Function

Sub filter()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht As String

'specify sheet name in which the data is stored
sht = "Sheet1"

'change filter column in the following code
' 行统计从F列改为B列
last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row
' 若你的数据列超出F列，可将下方范围调整为实际最大列，例如A1:Z & last
Set rng = Sheets(sht).Range("A1:F" & last)

' 高级筛选提取唯一值的列从F改为B
Sheets(sht).Range("B1:B" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True

For Each x In Range([AA2], Cells(Rows.Count, "AA").End(xlUp))
With rng
.AutoFilter
' 筛选字段序号从6（F列）改为2（B列）
.AutoFilter Field:=2, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.Paste
End With
Next x

' Turn off filter
Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

核心改动说明 #

• 数据行计数逻辑从F列切换为B列，避免因F列空值导致行数统计错误
• 高级筛选提取唯一值的范围从F列调整为B列，匹配新的拆分维度
• 自动筛选的字段序号从6（对应A列起始计数的第6列F）修改为2（对应第2列B），确保筛选目标列正确

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