Скрытие/отображение ненужных строк и столбцов
Постановка задачи
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 1. Скрытие строк и столбцов
Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide):
Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide).
Проблема в том, что с каждым столбцом и строкой придется возиться персонально, что неудобно.
Способ 2. Группировка
Если выделить несколько строк или столбцов, а затем выбрать в меню Данные - Группа и структура - Группировать (Data - Group and Outline - Group), то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):
Более удобный и быстрый способ - использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.
Такой способ скрытия ненужных данных гораздо удобнее - можно нажимать либо на кнопку со знаком "+" или "-", либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа - тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.
Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением - через меню Данные - Группа и структура - Создать структуру (Data - Group and Outline - Create Outline). К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline):
Способ 3. Скрытие помеченных строк/столбцов макросом
Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:
Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert - Module) и скопируем туда текст двух простых макросов:
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False 'отключаем обновление экрана для ускорения
For Each cell In ActiveSheet.UsedRange.Rows(1).Cells 'проходим по всем ячейкам первой строки
If cell.Value = "x" Then cell.EntireColumn.Hidden = True 'если в ячейке x - скрываем столбец
Next
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells 'проходим по всем ячейкам первого столбца
If cell.Value = "x" Then cell.EntireRow.Hidden = True 'если в ячейке x - скрываем строку
Next
Application.ScreenUpdating = True
End Sub
Sub Show()
Columns.Hidden = False 'отменяем все скрытия строк и столбцов
Rows.Hidden = False
End Sub
Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button).
Способ 4. Скрытие строк/столбцов с заданным цветом
Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:
Sub HideByColor()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Rows(2).Cells
If cell.Interior.Color = Range("F2").Interior.Color Then cell.EntireColumn.Hidden = True
If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True
Next
For Each cell In ActiveSheet.UsedRange.Columns(2).Cells
If cell.Interior.Color = Range("D6").Interior.Color Then cell.EntireRow.Hidden = True
If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:
...и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:
Sub HideByConditionalFormattingColor()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.
Ссылки по теме
- Что такое макрос, куда вставлять код макроса, как их использовать
- Автоматическая группировка в многоуровневых списках
Как написать код, чтобы эти два макроса выполнялись нажатием одной кнопки CommandButton,
а так же, что бы у данной кнопки менялось название в зависимости от статуса выполнения макроса,
то есть скрыть или отобразить.
Решение очень интересное, спасибо вам.
Воспользовалась Вашим макросом для скрытия столбцов и строк :
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Rows(1).Cells
If cell.Value = "x" Then cell.EntireColumn.Hidden = True
Next
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
If cell.Value = "x" Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Sub Show()
Columns.Hidden = False
Rows.Hidden = False
End Sub
Когда у меня скрыты нужные столбцы и строки, и мне нужно показать только строки - отображаются и скрытые столбцы ( и наоборот). Подскажите, пожалуйста, как до прописать, чтобы при отображении строк/ столбцов скрытые столбцы/строки не отображались. Заранее спасибо
PS: столбцы обозначила "y"
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Rows(1).Cells
If cell.Value = "х" Then cell.EntireColumn.Hidden = True
Next
For Each cell In ActiveSheet.UsedRange.Columns(1).Cells
If cell.Value = "х" Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Sub ShowC()
Columns.Hidden = False '(показывает скрытые столбцы)
End Sub
Sub ShowR()
Rows.Hidden = False '(показывает скрытые строки)
End Sub
For Each cell In ActiveSheet.Range("T15:T50").Cells If cell.Value = "x" Then cell.EntireRow.Hidden = TrueОтбой тревоге
Rows("15:50").Hidden = FalseСпасибо большое за статью, очень полезная и нужная информация.,
Хотел немного изменить макрос, чтобы скрывал все значения равные или больше 1 в столбике #3
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange.Columns(3).Cells
If cell.Value >= 1 Then cell.EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
Но выдаёт ошибку #13 mismatch, помогите пожалуйста разобраться.
Заранее спасибо,
С уважением, Александр.
Подскажите пожалуйста как можно сделать так чтобы данный макрос работал на определенном листе?
Применил код:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False For Each cell In ActiveSheet.Range("E9:E116").Cells If Not cell.Value <> "" Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Sub Show() Rows.Hidden = False End Sub4 Гб DDR4
обрабатывает даже при отключенном отображении пол минуты....
а когда написал код на активацию автоизменением листа через if, чтоб пробегал по ячейкам E9:E116 и скрывал строчки, если в диапазоне пустые ячейки и открывал строки, если не пустые ячейки, то ноут капитально повис... поэтому не могу привести тут тот код
Это нормально ?)
Можно как-то сделать автоматическое скрытие\открытие строчек при изменении ячеек в таблице но чтоб обрабатывалось минимальное время?
может через case быстрее будет? или еще какой способ?
Слегка модифицировал под свои нужды - чтобы при выполнении Show открывал не все строки, а только те, которые были скрыты предыдущим действием переключателя.
Мне тоже нужно было скрыть пустые ячейки в определенном столбце. И я не много изменил приведенный выше макрос. Также написал свои комментарий в скобках для вас. Буду рад если вам или кому то еще пригодится эти изменения.
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False 'отключаем обновление экрана для ускорения
For Each cell In ActiveSheet.UsedRange.Rows(1).Cells '(если не нужно скрыть столбцы удалите эту строку кода)
If cell.Value = "x" Then cell.EntireColumn.Hidden = True '(если не нужно скрыть столбцы удалите эту строку кода)
Next '(если удалили две верхних строк кода удалите и эту строку)
For Each cell In ActiveSheet.Range.("А2:А500).Cells '(проверяет столбец "А" диапазон строк с 2 до 500)
If cell.Value = "" Then cell.EntireRow.Hidden = True '(в место"х" оставил пусто, то есть скрывать пустые ячейки)
Next
Application.ScreenUpdating = True
End Sub
Sub Show()
Columns.Hidden = False 'отменяем все скрытия строк и столбцов
Rows.Hidden = False
End Sub
Большое спасибо за такой полезный сайт и за то, что Вы делаете! я здесь многому научился!
Но у меня все же есть вопрос по этому макросу.
Подскажите, пожалуйста, а если мне не надо, чтобы раскрывались все скрытые ячейки/столбцы, а раскрывались те столбцы/строки по тому же условию, по которому их же и скрывали?
можно ли написать макрос, чтобы показывались столбцы путем ввода значения шапки? ( есть 25 недель)- чтобы ввести номер недели или 2,3 недели ,и отображались только эти столбцы, остальные скрывались?
Для того чтобы скрывать ненужные строки в документе есть вот такой макрос.
В строках 32-37, которые скрываются макросом в зависимости от кейса есть чекбоксы (элемент управления формы), которые не скрываются вместе со строками.
Сами чекбоксы не должны приводить ни к каким действиям и предназначены только для того чтобы ставить/снимать галочку.
Прошу знатоков помочь, что необходимо дополнить в макросе, чтобы вместе со строками, которые скрываются также скрывались и чекбоксы, в которых находятся эти чекбоксы.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("B8")) Is Nothing Then Rows("9:9").Hidden = False Select Case [b8] Case "—" Rows("9:9").Hidden = True Case "Показать" Rows("9:9").Hidden = False End Select End If If Not Intersect(Target, Range("B26")) Is Nothing Then Rows("32:37").Hidden = False Select Case [b26] Case "Вариант 1" Rows("35:37").Hidden = True Case " Вариант 2" Rows("33:37").Hidden = True Case " Вариант 3" Rows("32:32").Hidden = True Rows("34:37").Hidden = True Case "Вариант 4" Rows("33:37").Hidden = True End Select End If End SubНекоторое время тому в поисках решения своей задачи излазил много тематических форумов.
Задача собственно была не особо мудреная.
Есть план работ на год, по дням, соответственно есть недели, месяцы.
Представлен в виде строки где в колонках даты.
Цель была - иметь возможность отображать конкретный месяц или неделю или период дат по желанию.
В идеале в первой ячейке строки месяцев выбирать месяц из выпадающего списка и видеть результат. То же самое по строке с неделями и датами.
Поиски привели к одному из решений от в посте от 02.02.2010 г.
Спасибо Igor67 !!!
Попытка прикрутить код макроса оказалась наполовину успешной - мог по желанию отображать что-то одно - или месяц или неделю, и то только меняя код. Как сделать дополнительные управляющие ячейки не разобрался - не хватило знаний. Хотел уже обращаться за помощью и открывать новую тему...
Помог сын, студент))
Услышал что надо, посмотрел код, почесал загривок и через полчаса выдал то, что получилось.
Хочу приложить этот вариант. Подскажите плиз как это сделать.
Надеюсь кому нибудь пригодится в его задачах.
Подскажите, пожалуйста, возможно ли макрос по цвету прописать сразу на несколько листов? Те при нажатии кнопки, на всех вкладках должны свернуться не нужные колонки.
Если можно, подскажите, как.
Вот макрос который автоматически при выделении ячейки (1 уровень группировки) открывает группу и скрывает остальные ячейки не входящие в нее.
Помогите пожалуйста понять в чем загвоздка и как решить эту проблему.
Мне нужен макрос который скрывает строки если все ячейки из нескольких столбцов пустые. Я использовала макрос написанный выше, спасибо Равшану Каримову. У меня несколько диапазонов (столбцов), а макрос работает даже если только 1 пустая ячейка - строка все равно скрывается. Мне надо, чтобы макрос скрывал строку если ВСЕ ячейки из ВСЕХ диапазонов (на этой строке) пусты, а если хотя бы 1 заполнена, то строку не скрывать.
Sub Hide()
Dim cell As Range
Application.ScreenUpdating = False 'отключаем обновление экрана для ускорения
For Each cell In ActiveSheet.Range("F4:F64,L4:L64,R4:R64,X4:X64").Cells '(проверяет столбец "F" диапазон строк с 4 до 64)
If cell.Value = "" Then cell.EntireRow.Hidden = True '(в место"х" оставил пусто, то есть скрывать пустые ячейки)
Next
Application.ScreenUpdating = True
End Sub
Sub Show()
Rows.Hidden = False
End Sub
Подскажите пожалуйста.
Я так и не разобрался.
Как скрывать Столбцы, в которых нет данных в определенном диапазоне ячеек каждого столбца?
https : //ibb.co/wLTbGJB
Вот на фото колонка L и в ней начиная с L4 вниз нету данных до L54.
И можно как-то по вашему макросу, сделать так чтобы именно такие колонки скрывались.
Чтобы можно было отфильтровав видеть заполненные колонки.