Excel — есть ли способ автоматически скрыть пустые столбцы при фильтрации строк?

Question

Я работаю с данными, импортированными из базы данных NOSQL.

Иногда мне попадаются рабочие листы с 1000 столбцов и более, в большинстве ячеек которых нет данных.

Я ищу способ, при котором при фильтрации данных и отображении только определенных строк все столбцы для видимых строк, в которых вообще нет данных, будут автоматически скрываться.

Таким образом, мне не придется горизонтально прокручивать сотни пустых столбцов в поисках информации.

Если у вас есть решение этой проблемы, я буду благодарен.

Спасибо,

Ханан Коэн

Answer 1

Этот код скроет столбцы, если заполнена только ячейка заголовка:

Sub KolumnHider()
    Dim wf As WorksheetFunction
    Dim i As Long, r As Range

    Set wf = Application.WorksheetFunction
    For i = 1 To 1000
        Set r = Cells(1, i).EntireColumn
        If wf.CountA(r) < 2 Then r.Hidden = True
    Next i
End Sub

Если заголовков столбцов нет, то сделайте так2в1.

Answer 2

Этот код VBA скроет все пустые столбцы независимо от того, есть ли у них заголовок.

Private Sub CommandButton1_Click()

  Dim rng As Range
  Dim nLastRow As Long
  Dim nLastColumn As Integer
  Dim i As Integer
  Dim HideIt As Boolean
  Dim j As Long

  Set rng = ActiveSheet.UsedRange
  nLastRow = rng.Rows.Count + rng.Row - 1
  nLastColumn = rng.Columns.Count + rng.Column - 1

  For i = 1 To nLastColumn
     HideIt = True

  For j = 2 To nLastRow

    If Cells(j, i).Value <> "" Then
      HideIt = False
    End If
  Next

    If HideIt = True Then
      Columns(i).EntireColumn.Hidden = True
    End If
  Next

End Sub

Примечания:

• Если вы используете стандартную команду фильтра из меню для фильтрации
  записей, сначала запустите этот код, а затем выполните фильтр.
• Если вы используете код VBA для фильтрации записей, сначала вызовите эту подпрограмму в своем коде. В этом случае лучше заменить "CommandButton1_Click" на какое-то другое имя, например "Private Sub HideBlankColumn()".

Answer 3

Я изменил код Rajesh S, чтобы позаботиться о примененных фильтрах, выйти из внутреннего цикла как можно скорее и отобразить столбцы, если примененные фильтры изменяют видимые строки. Запустите HydeEmptyColumns после фильтрации.

Public Sub HydeEmptyColumns()

  Dim rng As Range
  Dim nLastRow As Long
  Dim nLastColumn As Integer
  Dim i As Integer
  Dim HideIt As Boolean
  Dim j As Long

  Set rng = ActiveSheet.UsedRange
  nLastRow = rng.Rows.Count + rng.row - 1
  nLastColumn = rng.Columns.Count + rng.Column - 1

  For i = 1 To nLastColumn
    HideIt = True

    For j = 2 To nLastRow
      If Not Rows(j).Hidden Then
        If Cells(j, i).Value <> "" Then
          HideIt = False
          Exit For
        End If
      End If
    Next

    Columns(i).EntireColumn.Hidden = HideIt
  Next

End Sub