遍历范围 VBA

Question

寻找通过范围的简单循环(例如 A 列范围("A5：A15"((，如果该范围内有一个空白单元格，我需要隐藏与空白单元格/单元格关联的整行/行。

我在想这样的事情来适应各种范围，但出现"类型不匹配"错误。任何原因

Sub test()
    Dim rng As Range, cell As Variant, ar As Variant
    Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
    Dim MyArray(1 To 4) As Range
      With ThisWorkbook.Worksheets("sheet1")
      'Set MyArray = rng

       Set MyArray(1) = Range("O8:O17")
       Set MyArray(2) = Range("O55:O64")
       Set MyArray(3) = Range("G37:G46")
       Set MyArray(4) = Range("G89:G98")

        'ar = Array(Rng1, Rng2, Rng3, Rng4)
        'Set rng = .Range("O8:O17")
        For Each cell In MyArray
            If Len(cell.Value) < 1 Then
               cell.EntireRow.Hidden = True
            End If
        Next cell
    End With
End Sub

？

Answer 1

像

这样：

你可以把它放在一个主题中：

For Each cell In Range("A5:A15")
    If Len(cell.Value) < 1 Then
        cell.EntireRow.Hidden = True
    End If
Next
For Each cell In Range("A40:A55")
    If Len(cell.Value) < 1 Then
        cell.EntireRow.Hidden = True
    End If
Next

---

新答案 ：

Dim rng As Range, cell As Variant, ar As Variant
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range
Dim MyArray(1 To 4) As Range
  With ThisWorkbook.Worksheets("sheet1")
  'Set MyArray = rng

   Set MyArray(1) = Range("O8:O17")
   Set MyArray(2) = Range("O55:O64")
   Set MyArray(3) = Range("G37:G46")
   Set MyArray(4) = Range("G89:G98")

    'ar = Array(Rng1, Rng2, Rng3, Rng4)
    'Set rng = .Range("O8:O17")
Dim i As Integer
    For i = LBound(MyArray) To UBound(MyArray)
            For Each cell In MyArray(i)
             If Len(cell.Value) < 1 Then
               cell.EntireRow.Hidden = True
            End If
        Next
    Next
End With

Answer 2

尝试：

Option Explicit
Sub test()
    Dim rng As Range, cell As Range
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng = .Range("A5:A15")
        For Each cell In rng
            If cell.Value = "" Then
                .Rows(cell.Row).EntireRow.Hidden = True
            End If
        Next cell
    End With
End Sub

Answer 3

这充分利用了 Excel VBA 模型。我猜它比上面更快，但没有进行性能测试。

Dim Cell As Range
For Each Cell In Range("A5:A15").SpecialCells(xlCellTypeBlanks)
   Cell.EntireRow.Hidden = True
Next

Answer 4

尝试以下操作

Option Explicit
Sub youcouldhaveatleasttriedtodosomethingyourself()
Dim r1 As Range, r2 As Range, c As Range, target As Range
With Workbooks(REF).Sheets(REF)
    Set r1 = .Range("A1:A54")
    Set r2 = .Range("F3:F32")
    Set target = Application.Union(r1, r2) 
    For Each area In target.Areas
        For Each c In area
            If c.Value = vbNullString Then .Rows(c.Row).EntireRow.Hidden = True
        Next c
    Next area
End With
End Sub

请注意，我现在设置了两个示例范围。您始终可以向联合函数添加更多范围变量。