excel 隐藏空行

xdnvmnnf 于 2023-03-24 发布在其他

关注(0)|答案(1)|浏览(120)

我有工作代码来隐藏设置范围（B13到J 45）之间的空行：

For i = 13 To 45
    If Cells(i, "B") & Cells(i, "C") & Cells(i, "D") & Cells(i, "E") & Cells(i, "F") & Cells(i, "G") _
    & Cells(i, "F") & Cells(i, "G") & Cells(i, "H") & Cells(i, "I") & Cells(i, "J") = "" Then
        Rows(i).EntireRow.Hidden = True
    End If
Next i

当我更改行值时，我得到
类型不匹配/运行时错误“13”错误
这是我尝试运行的（唯一的区别是行范围，即58到81）：

For i = 58 To 81
    If Cells(i, "B") & Cells(i, "C") & Cells(i, "D") & Cells(i, "E") & Cells(i, "F") & Cells(i, "G") _
    & Cells(i, "F") & Cells(i, "G") & Cells(i, "H") & Cells(i, "I") & Cells(i, "J") = "" Then
        Rows(i).EntireRow.Hidden = True
    End If
Next i

1.为什么会发生这种情况，我该如何解决？
1.有没有更聪明的方法来做到这一点？

excel

来源：https://stackoverflow.com/questions/66228463/hide-rows-which-are-empty

1条答案

按热度按时间

oxcyiej71#

隐藏行

除了ActiveSheet，您可能更希望使用类似ThisWorkbook.Worksheets("Sheet1")的代码。
法典

Option Explicit

Sub Hide1()
    HideRows 13, 45
End Sub

Sub Hide2()
    HideRows 58, 61
End Sub

Sub HideRows( _
        ByVal FirstRow As Long, _
        ByVal LastRow As Long)
    Const ColsAddress As String = "B:J"
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim crg As Range: Set crg = ws.Columns(ColsAddress)
    Dim cCount As Long: cCount = crg.Columns.Count
    Dim trg As Range
    Dim i As Long
    For i = FirstRow To LastRow
        If Application.CountBlank(crg.Rows(i)) = cCount Then
            If trg Is Nothing Then
                Set trg = ws.Rows(i)
            Else
                Set trg = Union(trg, ws.Rows(i))
            End If
        End If
    Next i
    If Not trg Is Nothing Then
        trg.Rows.Hidden = True
    End If

End Sub

赞(0）回复(0）举报 2023-03-24

我来回答

excel 隐藏空行

1条答案

隐藏行

相关问题

热门标签

最新问答