根据多个范围单元格值VBA隐藏多行 [英] Hide multiple rows based on multiple ranges cell value VBA

问题描述

我在工作簿中有一个工作表(Sheet3)，我想编写VBA以根据多个范围中的单元格值隐藏多行. VBA必须经历两个不同的步骤；第一个是如果指定范围内的第一个单元格为空，则隐藏整个范围(范围1除外，因为第一个单元格永远不会为空).第二步将是如果范围中的第一个单元格不为空，则隐藏该范围中的行为空.具体如下:

I have a sheet (Sheet3) within a workbook that I would like to write VBA to hide multiple rows based on cell value in multiple ranges. The VBA would have to run through two different steps; the first would be if the first cell within the specified range is blank then hide the entire range (except range 1 since the first cell would never be blank). The second step would be if the first cell in range is not blank, then hide rows in that range that are blank. Here are the specifics:

范围1

1. A11:A60-隐藏范围空白的行

范围2

1. A71:A120-如果单元格A71为空白，则隐藏A71:A120.否则，隐藏范围A71:A120中所有空白的行.

范围3

1. A131:A180-如果单元格A131为空白，则隐藏A131:A180.否则，隐藏范围A131:A180中所有空白的行.

范围4

1. A191:A240-如果单元格A191为空白，则隐藏A191:A240.否则，隐藏范围A191:A240中所有空白的行.

范围5

1. A251:A300-如果单元格A251为空白，则隐藏A251:A300.否则，隐藏范围A251:A300中所有空白的行.

1. A251:A300 - If cell A251 is blank, Hide A251:A300. Otherwise hide all rows that are blank in range A251:A300.

Public Sub HideRowsSummary()
Dim wsMySheet As Worksheet
Dim lngMyRow  As Long, unionRng As Range
Application.ScreenUpdating = False
For Each wsMySheet In ThisWorkbook.Sheets
   Select Case wsMySheet.Name
    Case Is = Sheet3
        .Range("A11:A60", "A71:A120", "A131:A180", "A191:A240", "A251:A300").EntireRow.Hidden = False
            For lngMyRow = 11 To 60
                If Len(.Range("A" & lngMyRow)) = 0 Then
                    If Not unionRng Is Nothing Then
                        Set unionRng = Union(unionRng, .Range("A" & lngMyRow))
                    Else
                        Set unionRng = .Range("A" & lngMyRow)
                    End If
                End If
            Next lngMyRow
        End With
    End Select
    If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
    Set unionRng = Nothing
Next wsMySheet
Application.ScreenUpdating = True

结束子

推荐答案

在您的问题中，#2, 3, 4, 5都遵循类似的逻辑.

In your question #2, 3, 4, 5 all follow similar logic.

第一个i循环处理#1.代码的下一部分解决了#2.您只需复制/粘贴底部并更改测试值即可完成询问.

The first i loop tackles #1. The next portion of the code tackles #2. You can simply copy/paste the bottom portion and change your test values to complete your ask.

Option Explicit

Sub HideMe()

Dim i As Integer

With ThisWorkbook.Sheets("Sheet3")
    For i = 11 To 60
        .Range("A" & i).EntireRow.Hidden = .Range("A" & i) = vbNullString
    Next i

    'Repeat this portion for you other ranges that follow the same rules
    If .Range("A" & 71) = vbNullString Then
        .Range("A71:A120").EntireRow.Hidden = True
    Else
        For i = 72 To 120
            .Range("A" & i).EntireRow.Hidden = .Range("A" & i) = vbNullString
        Next i
    End If
End With

End Sub

---

这可以通过

---

This can be improved by

A)使用For Each循环而不是For i循环 B)与其一一隐藏行，不如将它们作为(Union)添加到行集合中，并一次隐藏所有Union

A) Use For Each loop instead of For i loop B) Instead of hiding rows one by one, add them to a collection of rows as a (Union) and hide the Union all at once

这篇关于根据多个范围单元格值VBA隐藏多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！