☰

宏修改工作表后如何保持页面上的行数不变

tag-icon • tag-icon microsoft-excel macros
宏修改工作表后如何保持页面上的行数不变

我正在制作一个页面，列出机械零件及其所有可能的组件变体，其中一些有超过 21 行。当我使用宏时，值为 0 的行会被删除，我可以打印它，但我需要将行数保持在 21 不变。有没有简单的解决方案来实现我想要的效果？

我使用的是 2004 版 Excel。英语不是我的母语，所以请耐心等待。

这是我用来隐藏或显示线条的宏

    '=========>>
Option Explicit
Option Compare Text

Sub CheckValore()

    If ActiveSheet.Range("$n$2").Value = "FALSO" Then
        VisualizzaRighe
        ActiveSheet.Range("$n$2").Value = "VERO"
    Else
        NascondiRigheVuote
        ActiveSheet.Range("$n$2").Value = "FALSO"
    End If


End Sub

'--------->>
Sub NascondiRigheVuote()
    'Dim WB As Workbook
    Dim SH As Worksheet
    Dim Rng As Range
    Dim i As Long, LRow As Long


    Const sColonnaSi As String = "N"             '<<=== Modifica
    Const iPrimaRigaDati As Long = 4             '<<=== Modifica
    Const sColonneDaStampare As String = "A:M"   '<<=== Modifica"

    'Set WB = ThisWorkbook
    Set SH = ActiveSheet
    
    With SH
        LRow = LastRow(SH, .Columns(sColonneDaStampare))
        On Error GoTo XIT
        Application.ScreenUpdating = False
        For i = iPrimaRigaDati To LRow
            With .Rows(i)
                .Hidden = .Cells(1, sColonnaSi) <> "si"
            End With
        Next i

        With .PageSetup
            .Orientation = xlLandscape
            .PrintArea = Intersect(.Rows(1).Resize(LRow), _
                                   .Columns(sColonneDaStampare))
        End With
    End With
XIT:
    Application.ScreenUpdating = True
End Sub

'--------->>
Sub VisualizzaRighe()
    ActiveSheet.Rows.Hidden = False
End Sub

'--------->>
Function LastRow(SH As Worksheet, _
                 Optional Rng As Range, _
                 Optional minRow As Long = 1, _
                 Optional sPassword As String)
    Dim bProtected As Boolean

    With SH
        If Rng Is Nothing Then
            Set Rng = .Cells
        End If
        bProtected = .ProtectContents = True
        If bProtected Then
            .Unprotect Password:=sPassword
        End If
    End With
    On Error Resume Next
    LastRow = Rng.Find(What:="*", _
                       after:=Rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
    On Error GoTo 0
    If LastRow < minRow Then
        LastRow = minRow
    End If

    If bProtected Then
        SH.Protect Password:=sPassword, _
                   UserInterfaceOnly:=True
    End If
End Function

'<<========= 这是之前的页面：
宏之前

这是之后的情况。我需要每页包含信息都是 21 行，并且不打印每页没有字段的页面。

宏之后 期望的打印结果

相关内容