隐藏空行

Question

我有工作代码来隐藏设置范围(B13到J45)之间的空行:

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. 为什么会发生这种情况，我该如何修复它?
2. 是否有更聪明的方法来做到这一点?

Answer 1

隐藏行

• 比起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