☰

해당 행의 연속되지 않은 여러 열의 모든 셀이 비어 있다는 기준에 따라 Excel 행을 숨기는 방법

tag-icon • tag-icon microsoft-excel vba
해당 행의 연속되지 않은 여러 열의 모든 셀이 비어 있다는 기준에 따라 Excel 행을 숨기는 방법

Excel에서 행의 연속되지 않은 여러 열의 모든 셀이 비어 있는 경우에만 행을 숨길 수 있습니까? 내 워크시트에는 약 300개의 열이 있으므로 이 작업을 수행할 때마다 하나씩 개별적으로 클릭할 수는 없습니다.

아래 VBA 코드를 이미 시도했지만 두 개 이상의 범위를 허용하지 않습니다. 감사합니다.

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:

아래 OP 설명에 따라 열을 숨기는 두 번째 버전(v2.1):

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.

* 시트를 편집할 때 자동으로 숨겨진 행을 실행 취소하는 기능을 잃게 됩니다. 필요한 경우 이 문제를 해결할 수 있습니다.

관련 정보