Excel VBA宏修改需求：添加G列筛选条件并导出指定列至TXT

Perfect, let's adjust your existing VBA macro to include the required G column (Field:=7) filter for value "38"—and ensure we only process rows that meet this condition throughout the workflow. Here's the updated code:

Sub ExceltoTXT()
    Dim sh As Worksheet, c As Range, ky As Variant, wb As Workbook, wPath As String, lr As Long
    Application.SheetsInNewWorkbook = 1
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set sh = ActiveSheet
    wPath = ThisWorkbook.Path & "\"
    
    ' Clear any existing filters first
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    
    ' Insert the exact requested G column filter line
    sh.Range("A1").AutoFilter Field:=7, Criteria1:="38"
    
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    
    ' Collect unique E values ONLY from rows where G=38 (visible rows)
    With CreateObject("scripting.dictionary")
        ' Iterate only visible cells in E column to avoid invalid E values
        For Each c In sh.Range("E2:E" & lr).SpecialCells(xlCellTypeVisible)
            .Item(c.Value) = Empty
        Next
        
        For Each ky In .Keys
            ' Apply filter for current E column unique value
            sh.Range("E1").AutoFilter Field:=1, Criteria1:=ky
            
            ' Copy only visible F column rows (meets both G=38 AND E=ky)
            Set wb = Workbooks.Add
            sh.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible).Copy
            wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
            
            ' Save to TXT and close
            wb.SaveAs wPath & ky & ".txt", FileFormat:=xlTextMSDOS
            wb.Close False
            
            ' Clear E column filter for next iteration
            sh.Range("E1").AutoFilter Field:=1
        Next
    End With
    
    ' Reset filters and Excel settings
    sh.ShowAllData
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Key Changes & Explanations: #

• Added the required G column filter: Right after clearing existing filters, I inserted the exact line you specified: sh.Range("A1").AutoFilter Field:=7, Criteria1:="38" to isolate rows where G column equals "38".
• Restricted unique E values to valid rows: The original macro collected every E value in the column (including those from rows where G≠38). Now we use .SpecialCells(xlCellTypeVisible) to only gather unique E values from rows that passed the G=38 filter.
• Exported only relevant F column data: When copying F column values, we again use .SpecialCells(xlCellTypeVisible) to ensure we only export data from rows that meet both filters (G=38 AND E=current unique value).
• Cleaned up filters between iterations: Added a line to clear the E column filter after each export, so the next unique value filter works correctly.
• Restored default Excel settings: Turned ScreenUpdating and DisplayAlerts back on at the end (a best practice to avoid leaving Excel in a modified state if the macro errors out).

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