☰

行内の連続していない複数の列のすべてのセルが空白であるという条件に基づいて Excel の行を非表示にする方法

tag-icon • tag-icon microsoft-excel vba
行内の連続していない複数の列のすべてのセルが空白であるという条件に基づいて Excel の行を非表示にする方法

Excel で、行内の連続していない複数の列のすべてのセルが空白の場合にのみ、行を非表示にするにはどうすればよいでしょうか。私のワークシートには約 300 列あるため、これを行うたびに 1 つおきに個別にクリックすることはできません。

以下の VBA コードはすでに試しましたが、2 つ以上の範囲は許可されません。ありがとうございます。

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160913
Dim xRg As Range
Application.ScreenUpdating = False
    For Each xRg In Range("B1:B825","D1:D825","F1:F825")
        If xRg.Value = "" Then
            xRg.EntireRow.Hidden = True

        Else
            xRg.EntireRow.Hidden = False
        End If
    Next xRg
Application.ScreenUpdating = True
End Sub

答え1

これを試して

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long

    Application.ScreenUpdating = False
    With UsedRange
        For c = 2 To .Columns.Count Step 2
            .AutoFilter Field:=c, Criteria1:="<>"
        Next
    End With
    Application.ScreenUpdating = True
End Sub

答え2

チェックする列が多数ある場合、次の一般化されたソリューションによりコード入力が簡素化されます。

Private Sub Worksheet_Change(ByVal Target As Range)

  Const strcRowExtent As String = "1:825"
  Const strcColExtent As String = "B:BDB"

  Dim boolHideRow As Boolean
  Dim lngFirstColNumber As Long
  Dim rngRow As Range
  Dim rngVisibleRowExtent As Range
  Dim rngColumn As Range
  Dim rngColExtent As Range

  Set rngVisibleRowExtent = Range(strcRowExtent).SpecialCells(xlCellTypeVisible)
  Set rngColExtent = Range(strcColExtent)
  lngFirstColNumber = rngColExtent.Column
  Application.ScreenUpdating = False
    For Each rngRow In rngVisibleRowExtent.Rows
      boolHideRow = True
      For Each rngColumn In rngColExtent.Columns
        If (rngColumn.Column - lngFirstColNumber) Mod 2 = 1 Then
          'Skip every second column
        ElseIf rngColumn.Cells(rngRow.Row).Value2 <> "" Then
          boolHideRow = False
          Exit For
        End If
      Next rngColumn
      If boolHideRow Then Rows(rngRow.Row).EntireRow.Hidden = boolHideRow
    Next rngRow
  Application.ScreenUpdating = True

End Sub

説明：

最初に、表示される行のセットが行の完全なセットから抽出されます。これにより、速度が大幅に向上します。*

次に、このコードは表示されている行のセットをループします。各行について、適切な列をループして、空白でない値をチェックし、ない最初の行が見つかるとすぐに行を非表示にします。(行を非表示にするのは、該当する列がすべて空白の場合にのみ実行されるデフォルトのアクションです。)

編集#2:

2 番目のバージョン (v2.1) では、以下の OP コメントに従って、列も非表示になります。

Private Sub Worksheet_Change(ByVal Target As Range)
  ' v2.1
  Const lngcSkipRows As Long = 4
  Const strcRowExtent As String = "1:825"
  Const strcColExtent As String = "B:BDB"

  Dim boolHideRow As Boolean
  Dim lngFirstColNumber As Long
  Dim rngRow As Range
  Dim rngVisibleRowExtent As Range
  Dim rngColumn As Range
  Dim rngColExtent As Range
  Dim rngCol As Range
  Dim rngVisibleColExtent As Range
  Dim rngCroppedCol As Range

  Application.ScreenUpdating = False
    ' Hide rows
    Set rngVisibleRowExtent _
      = Range(strcRowExtent).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
    Set rngColExtent = Range(strcColExtent)
    lngFirstColNumber = rngColExtent.Column
    For Each rngRow In rngVisibleRowExtent.Rows
      boolHideRow = True
      For Each rngColumn In rngColExtent.Columns
        If (rngColumn.Column - lngFirstColNumber) Mod 2 = 1 Then
          'Skip every second column
        ElseIf rngColumn.Cells(rngRow.Row).Value2 <> "" Then
          boolHideRow = False
          Exit For
        End If
      Next rngColumn
      If boolHideRow Then Rows(rngRow.Row).EntireRow.Hidden = boolHideRow
    Next rngRow
    'Hide Columns
    Set rngVisibleColExtent _
      = Range(strcColExtent).Rows(1).SpecialCells(xlCellTypeVisible).EntireColumn
    For Each rngCol In rngVisibleColExtent.Columns
      Set rngCroppedCol _
        = rngCol _
          .Resize(Range(strcRowExtent).Rows.Count - lngcSkipRows) _
          .Offset(lngcSkipRows)
      If WorksheetFunction.CountA(rngCroppedCol) = 0 Then rngCol.Hidden = True
    Next rngCol
  Application.ScreenUpdating = True

End Sub

説明：

非表示の行がある場合に表示されている列のセットを抽出するには (およびその逆)、抽出式を少し変更する必要があることがわかりました。

表示されている列のセットをループするコードは、内部ループが必要ないため、行のコードよりも簡単です。CountA()代わりに、ワークシート関数が使用されます。

すべて空白の非表示でない列がまだあるように見える場合があることに注意してください。これらの列には、非表示の行に値があります。厳密には、コメントに従って、これらの列を非表示にしないのは意図的です。

注: 変数の命名規則について知りたい場合は、次の規則に基づいています。RVBA。

*シートの編集時に自動的に非表示になった行を元に戻す機能が失われます。必要に応じて修正できます。

関連情報