如何修改VBA代码跳过Excel对应D列空白行以生成合规薪资导入TXT文件？

修改后的完整代码 #

Dim thisWS As Worksheet
Set thisWS = Sheet1
Dim LastRow As Long
Dim workArea As Range
With thisWS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set workArea = .Range(.Cells(2, 2), .Cells(LastRow, 7))
End With

Dim saveFileName As String
saveFileName = Application.GetSaveAsFilename("2024Testing", "text files (*.txt), *.txt")

' 用户取消保存时退出程序
If saveFileName = "False" Then Exit Sub

Open saveFileName For Output As #1

Dim i As Integer
Dim j As Integer
Dim lineText As String

For i = 1 To workArea.Rows.Count
    ' 检查原表格对应行的D列（workArea第i行对应原表第i+1行，D列为第4列）
    If Trim(thisWS.Cells(i + 1, 4).Value) = "" Then
        ' D列为空，跳过该行处理
        GoTo SkipRow
    End If
    
    lineText = "" ' 重置行文本，避免累积上一行内容
    For j = 1 To workArea.Columns.Count
        lineText = IIf(j = 1, "", lineText & ",") & workArea.Cells(i, j)
    Next j
    lineText = Left$(lineText, Len(lineText) - 2)
    Print #1, lineText
    
SkipRow:
Next i
Close #1

MsgBox "Bonus Import File Created"
End Sub

核心修改点 #

• 空白行判断逻辑：通过Trim(thisWS.Cells(i + 1, 4).Value) = ""检查原表格中对应行的D列是否为空（Trim用于过滤单元格内的空格，避免误判），为空则直接跳过该行的导出操作。
• 重置行文本变量：每次循环开始时将lineText清空，防止上一行的内容残留到当前行，导致输出内容错误。
• 增加取消保存处理：添加判断语句，当用户取消保存对话框时直接退出程序，避免后续代码报错。

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