如何将HideRow宏集成到PrintPDFs宏，实现打印PDF前隐藏指定行

我需要给每位员工打印标准格式信函，自己宏编程经验有限，现有两个可独立运行的基础Excel宏：

• PrintPDFs宏：遍历命名区域EmployeeNo中的员工编号，将CellWithDropdown单元格设为对应编号，把指定打印区域导出为PDF并保存至用户选择的文件夹；
• HideRow宏：可隐藏指定范围内的对应行。

希望在打印每位员工的PDF前，先执行HideRow宏隐藏该员工不适用的行，请求帮助将HideRow宏集成到PrintPDFs宏中。原宏代码如下：

原PrintPDFs宏代码 #

Sub PrintPDFs()
Const NamedRangeName = "EmployeeNo"
Const SheetName = "Letter"
Const CellWithDropdown = "M25"
Const PrintRange = "A15:I60"
Dim rowCounter As Integer

Dim fPath As String, choice As Range, fName As String
'allow user to select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With

'rest of code
    If fPath <> "" Then
        With Sheets(SheetName)
                For Each choice In Range(NamedRangeName)
                .Range(CellWithDropdown) = choice
                 fName = choice & Format(Date, " yymmdd") & ".pdf"
                .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
            Next
        End With
    Else
        MsgBox "No folder selected"
        
    End If

End Sub

原HideRow宏代码 #

Sub HideRow()
LineStart = 35
LineEnd = 52
ColumnNumber = 12
For i = LineStart To LineEnd
If Cells(i, ColumnNumber).Value <> "Zero" Then
Cells(i, ColumnNumber).EntireRow.Hidden = False
Else
Cells(i, ColumnNumber).EntireRow.Hidden = False
End If
Next i
End Sub

注意事项与修正说明 #

原HideRow宏存在逻辑错误：无论单元格值是否为"Zero"，都设置行可见（EntireRow.Hidden = False），完全起不到隐藏行的作用。正确逻辑应为当单元格值为"Zero"时隐藏对应行，同时要限定操作的工作表，避免跨表出错。

整合后的完整宏代码 #

Sub PrintPDFsWithRowHiding()
    Const NamedRangeName = "EmployeeNo"
    Const SheetName = "Letter"
    Const CellWithDropdown = "M25"
    Const PrintRange = "A15:I60"
    ' 隐藏行相关参数
    Const HideRowStart = 35
    Const HideRowEnd = 52
    Const CheckColumn = 12 ' 第12列即L列
    
    Dim fPath As String, choice As Range, fName As String
    Dim ws As Worksheet
    Dim i As Integer
    
    ' 让用户选择保存文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "选择PDF导出的目标文件夹"
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With
    
    If fPath <> "" Then
        Set ws = Sheets(SheetName)
        For Each choice In Range(NamedRangeName)
            ' 1. 设置当前员工编号
            ws.Range(CellWithDropdown) = choice
            
            ' 2. 执行隐藏行操作（修正原逻辑）
            For i = HideRowStart To HideRowEnd
                If ws.Cells(i, CheckColumn).Value = "Zero" Then
                    ws.Cells(i, CheckColumn).EntireRow.Hidden = True
                Else
                    ws.Cells(i, CheckColumn).EntireRow.Hidden = False
                End If
            Next i
            
            ' 3. 导出为PDF
            fName = choice & Format(Date, " yymmdd") & ".pdf"
            ws.Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=fPath & "\" & fName
        Next choice
    Else
        MsgBox "未选择文件夹"
    End If
End Sub

使用说明 #

1. 打开Excel的VBA编辑器（按Alt+F11），删除原有的两个宏，粘贴上面的整合版宏；
2. 确认Letter工作表存在，命名区域EmployeeNo已正确定义；
3. 测试时可先选择少量员工编号，验证行隐藏逻辑和PDF导出是否正常。

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