Vba excel скрыть строку
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 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) и скопируем туда текст двух простых макросов:
Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button) .
Способ 4. Скрытие строк/столбцов с заданным цветом
Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:
Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:
. и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:
Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.
Довольно часто появляется необходимость в Excel скрывать или отображать строки или столбцы. Особенно это актуально, когда на листе размещается очень много информации и часть из них является вспомогательной и требуется не всегда и тем самым загромождает пространство, ухудшая восприятие. Если это приходится делать часто, то делать это с помощью меню неудобно, особенно если приходится скрывать и отображать разные столбцы и строки.
Для существенного удобства можно написать простенький макрос, привязав его к кнопке и делать это одним щелком мыши.
Вот так выглядят простые примеры, с помощью которых Вы без труда сможете скрывать или отображать строки и столбцы с помощью VBA
Пример 1: Скрыть строку 2 в Excel
Sub HideString() ‘Это название макроса
Rows(2).Hidden = True
End Sub
Пример 2: Скрыть несколько строк в Excel (строку 3-5)
Sub HideStrings()
Rows(«3:5»).Hidden = True
End Sub
Пример 3: Скрыть столбец 2 в Excel
Sub HideCollumn()
Columns(2).Hidden = True
End Sub
Пример 4: Скрытие нескольких столбцов в Excel
Sub HideCollumns()
Columns(«E:F»).Hidden = True
End Sub
Пример 5: Скрытие строки по имени ячейки в Excel
Sub HideCell()
Range(«Возможности Excel»).EntireRow.Hidden = True
End Sub
Пример 6: Скрытие нескольких строк по адресам ячеек
Sub HideCell()
Range(«B3:D4»).EntireRow.Hidden = True
End Sub
Пример 7: Скрытие столбца по имени ячейки
Sub HideCell()
Range(«Возможности Excel»).EntireColumn.Hidden = True
End Sub
Пример 8: Скрытие нескольких столбцов по адресам ячеек
Sub HideCell()
Range(«C2:D5»).EntireColumn.Hidden = True
End Sub
Как видите процесс автоматического скрытия строк и столбцов очень прост, а применений данному приему огромное множество.
Для того, чтобы отобразить строки и столбцы в Excel вы можете воспользоваться этими же макросами, но вместе True необходимо указать False
Например, макрос для того, чтобы отобразить строку 2 будет выглядеть следующим образом:
Sub ViewString()
Rows(2).Hidden = False
End Sub
Надеемся, что данная статья была полезна вам и ответила на вопрос: как скрыть или отобразить строки и столбцы в Excel с помощью VBA
Спасибо за внимание.
Related posts:
Похожие статьи
Что делать если условием таблицы является скрытие столбцов по условию: содержание в ячейках определенного значения? Нужен макрос? Можете помочь?
Например: Есть таблица со строками содержащими в заголовке название рыб (200 строк) и столбцами содержащими в заголовке название наживки для рыб(50 столбцов). В таблице есть диапазон содержащий в ячейках символ Х и символ Y. Используя стандартный фильтр выбираю в одном из столбцов «фильтровать по значению Х» Требуется: Выделив все ячейки оставшегося после фильтрации диапазона(например осталось только 20 строк названий рыб и все 50 столбцов названий наживки) скрыть СТОЛБЦЫ в которых ячейки диапазона не содержат хотя бы 1 символ Х (например в результате получиться 20 строк названий рыб и всего 5 СТОЛБЦОВ названий наживки)
Добрый день!
Выдает ошибку на многоточие и макрос не срабатывает 🙁 как быть в этой ситуации.
Пример 4: Скрытие нескольких столбцов в Excel
Sub HideCollumns()
Columns(«E:F»).Hidden = True
End Sub
Здравствуйте!
Подскажите, пжл, что делаю не так .
В примере №7, выдаёт ошибку 400, пишет:
«Method ‘Range’of object’_Worksheet’failed»
Подскажите, пожалуйста, как сделать, чтобы в примере №7, макрос ссылался бы НЕ на ИМЯ ячейки, а на ЗНАЧЕНИЕ, которое есть в ячейке ?
Иными словами, если ячейки А1, А2, А3 — содержать значение «хотим скрыть эти строки», то макрос скрывает эти строки (т.е. — строки 1, 2 и 3).
Если в какой-либо ячейке — иное значение, то, соответственно, эта строка НЕ скрывается.
Зараенее спасибо за ответ.
Спасибо за макрос!
Но адаптировать под себя — оказалось для меня слишком сложно ((.
Буду благодарен, если подскажите, как реализовать такую процедуру:
На ЗАЩИЩЁННОМ листе — две кнопки «Скрыть» и «Отобразить»
При нажатии кнопки «Скрыть» — происходит скрытие всех строк, одна из ячеек которых (скажем, все такие ячейки расположены в одном столбце «D») содержит значение «Счёт закрыт».
Лист при этом остаётся ЗАЩИЩЁННЫМ.
И, соответственно, при нажатии кнопки «Отобразить» — все скрытые строки — отображаются. Лист также остаётся защищённым.
Заранее спасибо !
А вот такой вопрос. Есть диапазон в 31 столбец, как скрыть столбцы выходящие за предел диапазона в зависимости от продолжительности месяца. то есть, если, к примеру февраль, то скрыть 3 или 2 последних столбца в диапазоне. Заранее спасибо за ответ !
Можно воспользоваться простеньким макрос скрытия столбцов по определенному условию (в данном случае, если в перовой строке есть цифра 1).
Sub Col() ‘Скрыть столбцы
Application.ScreenUpdating = False
For Each s In Cells(1, 1).CurrentRegion.Rows(1).Cells
Columns(s.Column).EntireColumn.Hidden = s.Value = 1
Next s
Application.ScreenUpdating = True
End Sub
Sub all() ‘Отобразить все
Application.ScreenUpdating = False
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
Как использовать флажок, чтобы скрыть / показать строки или столбцы в Excel?
Предположим, вам нужно использовать флажок, чтобы скрыть или показать определенные строки или столбцы. Например, когда установлен флажок Active X Control, отображаются определенные строки или столбцы, в противном случае они будут скрыты. В этой статье показано, как использовать флажок, чтобы скрыть / показать строки или столбцы в Excel с подробностями.
You may interest in:
With the Kutools for Excel's Batch Insert Check Boxes/Option Buttons or Batch Delete Check Boxes/Option Buttons utilities, you can insert or delete multiple check boxes or option buttons in bulk in your Excel worksheet. See below screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More Download the free trial now
Используйте флажок, чтобы скрыть / показать строки или столбцы с кодом VBA
Следующий код VBA может помочь вам скрыть / показать определенные строки или столбцы с помощью флажка.
1. После установки флажка Active X Control на рабочий лист щелкните его правой кнопкой мыши и выберите Просмотреть код из контекстного меню. Смотрите скриншот:
2. в Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код VBA в окно кода.
Код VBA: используйте флажок, чтобы скрыть / показать строки или столбцы
Заметки:
1. В коде VBA [CD]. Весь столбец означает, что столбцы C и D будут скрыты или отображены, если установить или снять соответствующий флажок.
2. Чтобы скрыть или отобразить определенные строки, такие как строка 6: 9, измените [ CD]. Весь столбец в [6: 9]. Весь ряд в коде VBA.
3. нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.
4. Теперь выключите Режим проектирования под разработчик вкладка, как показано ниже:
С этого момента, когда флажок установлен, отображаются указанные строки или столбцы. Если этот флажок не установлен, указанные строки или столбцы скрыты.
В третьем варианте для скрытия строк применён автофильтр.
Комментарии
Подскажите, пожалуйста, возможно ли в LinkedCell вместо "ИСТИНА" написать "1" (когда флажок установлен), вместо "ЛОЖЬ" - "2" (флажок снят)?
Здравствуйте! Пробовал разобраться во втором варианте (вкладка "нормальные чекбоксы"). В случае когда большое количество строк с чекбоксами (допустим 150 строк, а не 5 как в примере), макрос для скрытия выполняется очень долго. Тажке, скорость его выполнения почему-то зависит от наличия значений на других листах (чем больше значений на других листах, тем он медленнее). Что можно сделать, чтобы скорость макроса была выше при большом количестве скрываемых строк?
Ну так понятно, что должны быть в книге эти объекты, - и галочка с именем CheckBox1, и подключение с именем "http://excelvba.ru"
А если их нет, - конечно ошибку будет макрос выдавать
Если сами не разберетесь, - можем сделать под заказ
Игорь так же Ошибка "Run-time error 424 object required на строке:
CheckBox1.Enabled = False: ActiveWorkbook.Connections("http://excelvba.ru").Refresh
Может необходимо создание объекта на запрос? ,но я могу быть ошибаться.
Как-то так можно сделать:
Помогите пожалуйста с кодом, выдает ошибку 424 ,((
Задача при проставленной галочке в чекбоксе макрос дает запрос, если нет галочки переходим на следующий запрос.
Sub CheckBox()
If CheckBox1.Value = True Then
ActiveWorkbook.Connections("http://excelvba.ru").Refresh
CheckBox1.Enabled = False
End If
End Sub
Sub CheckBox()
If CheckBox2.Value = True Then
ActiveWorkbook.Connections("http://abvlecxe.ru").Refresh
CheckBox1.Enabled = False
End If
End Sub
Если бы так просто. Это поле неактивно (серое) в свойствах чекбокса. Поэтому при сортировке перемещаются только ячейки, чекбоксы на месте стоят.
Как его "пометить", желательно программно - хз.
Обходной вариант - перерисовывать чекбоксы после сортировки, но как отловить эту самую сортировку.
Здравствуйте, Андрей
Выделите все чекбоксы, и в свойствах поменяйте режим привязки к ячейкам (опция «перемещать и изменять вместе с ячейками»)
Подскажите пожалуйста, как привязать чекбокс к ячейке? Проблема в следующем: на листе есть автофильтр один из столбцов которого реализован в виде чекбоксов. все прекрасно работает при фильтрации строк, но если применить сортировку то привязка чекбокса "слетает". Например: Автофильтр 50 строк столбец "А" - чекбоксы связанные (.LinkedCell="$A$2) с ячейками в которые вписаны. После применения сортировки в автофильтре чекбоксы становятся связаны с произвольной ячейкой столбца "А". (чекбокс изначально нарисованный и связанный с ячейкой "А2" после сортировки становится связан с ячейкой "А9", но рисуется по прежнему в "А2")
Юля, а какое отношение ваш вопрос имеет к теме статьи?
Макрос для вашего случая (делает выделенные ячейки полужирными)
как написать макрос с помощу которого все ячейки на текущем месте будут менять начертание на полужирный, подскажите
Сергей, я же не телепат, чтобы, не видя вашего файла, подсказать, в чем там проблема, и что написать надо.
В моём же примере всё работает - возьмите код оттуда.
И обратите внимание - чекбоксы (галочки) бывают двух видов, используйте тот код, который идёт для используемых вами чекбоксов.
Если сами не разберетесь - оформляйте заказ, прикрепляйте файл, объясняйте, как что должно работать, - тогда сам все сделаю (не бесплатно)
Доброй ночи!
смотрю и никак не пойму с чекбоксами. имеется ряд столбцов (согласно вашего примера делаем две кнопки - скрыть и отобразить) с яцейки столбцов ставим чек боксы. далее не пойму что и где писать что б работала кнопка спрятать те что без галочки. Помогите пожалуйста если не затруднит (офис 2007 винда 7/64)
С уважением, Сергей!
Вообще-то данные из Access у меня загружаются именно при активации листа, а потом таблицы динамически обновляются и изменяются при переключении по гипперссылкам (своего рода меню такое), при этом один и тот же лист может быть активным, поэтому галочки проставляются как при активации листа, так и при прохождении по пунктам моего меню. Судя по всему ОДИН раз галочки проставить не получится
Код можно писать куда угодно - в любой модуль. Вызывать его можно тоже из любой процедуры.
Вот вариант кода, который не будет удалять элементы ActiveX, и не будет ошибочно ставить галочки в ячейку a1
PS: Зачем каждый раз при активации листа заново проставлять все галочки?
ОДИН РАЗ запустите код (после импорта данных из access) - и больше не надо.
Простите, возникли вопросы, куда писать этот код? Где вызывать процедуры? Почему у меня в книге удалились элементы ActiveX (в основном Data Picker 6.0)? Почему чекбокс стоит в ячейке A1 на всех листах?
P.S. Код сначала вставила в модуль книги, потом перенесла его в обычный, который отвечает за интерфейс и вызвала процедуру в WorkSheet_Activаte
Спасибо Вам, Игорь, огромное. Смотрю на код и понимаю, что сама бы я до такого не додумалась. Вернее гипотетически я понимаю что нужно сделать, а как на VBA записать еще толком не знаю. Еще раз СПАСИБО!
Сделал я вам макрос для замены логических значений на галочки,
без изменения значений в ячейках.
Работает для всех листов активной книги.
Результат (слева - что было, справа - что стало)
Да для простого отображения, не оч. красиво смотрится ИСТИНА или ЛОЖЬ в ячейках, гораздо красивее галочки (а вот какие лучше использовать, подскажите пожалуйста Вы: мне больше понравились те, которые шрифтом делаются, но так, Вы говорите, нужно менять значение ячеек). Так как таблица заполняется данными из БД Access, то ничего естественно менять в данных не нужно, просто показать, причем макрос (т.е. процедура) наверное должен быть глобальным, так как таблицы будут все из БД и в некоторых из них будут логические поля(столбцы). Вообщем при активации листа, после выполнения
где LO - объект "умная таблица"
просматриваем таблицу на наличие логических значений и там где ИСТИНА, отображаем галочку, а где ЛОЖЬ - пустая ячейка соответственно, при этом значения в ячейке не меняем, т.к. в БД должны сохраниться логические значения. Но тогда придется с ячейками связывать чекбоксы, а это будет по-любому притормаживать.(Не хотелось бы). Данные будут редактироваться либо формой, либо (что скорее всего) элементами ActiveX над самой таблицей, строки будут изменяться, удаляться или добавляться в таблицу динамически, и только после этого сохраняться обратно в БД.
А какие «галочки» вас интересуют?
Который выполнены при помощи чекбоксов, или буквой «a» шрифтом Marlett?
Сделать-то можно, но это будет макрос, заменяющий в таблице одни значения другими
Обратную операцию (возврат логических значений в ячейки) тоже надо будет делать, при активации любого другого листа?
Создаваемые «галочки» должны менять значение ячеек?
Можно написать макрос, который всем логическим ячейкам назначит белый цвет шрифта, и поверх каждой такой ячейки нарисует чекбокс, связанный с этой ячейкой.
Но - при большом объёме данных это будет заметно тормозить (тысячи графических объектов на листе заметно снизят скорость работы с данными)
Для чего это надо сделать? (для удобства работы, для вывода на печать, ещё что-то?)
Может, есть какой-то более простой способ.
Здравствуйте, Игорь!
У меня такой вопрос, а если необходимо отображать галочки не по щелчку мышки, а сразу при активации листа, как это сделать программно?
То есть имеем ListObject, и в ней определенные столбцы логического типа. При создании и отображении в ячейках пишутся значения true и false соответственно. Можно ли сделать так, чтобы отображались ваши "галочки"? Грубо говоря, нужно обойти все ячейки в таблице (независимо от количества строк и столбцов) и превратить значения true в "галочки"
Здравствуйте, Елена.
Какую ещё мою процедуру, в какой исходный код, зачем?
Я лишь показал вам пример кода VBA для работы с «умной таблицей» (списком Excel 2003).
Этот код не создаёт таблицу, а работает с ней.
Вам на форум по Excel лучше обратиться, - там вам подскажут (я с СУБД пока не дружу).
Да и вопрос ваш не имеет никакого отношения к теме статьи.
Если строка Set LO = ActiveSheet.ListObjects(1)
выдаёт такую ошибку, значит, на активном листе Excel нет ни одной «умной таблицы».
Здравствуйте, Игорь.
Похоже, что "умная таблица" оказалась умнее меня :(. Попробовала поюзать ваш код-пример. В строке
Set LO = ActiveSheet.ListObjects(1)
пишет ошибку run-time error '9' Subscript out of range
Что не так?
У меня Office 2010 Windows7
Ой, спасибо, Вам, Игорь, большое за быстрый ответ. Дальше я с этой таблицей сама разбираться буду. Ух, я ее. А то в последнее время только она меня. (Шутка)
Извините за оффтоп и еще раз огромное спасибо
Вот вам пример кода для работы с «умной таблицей»:
(в Excel 2003 эта умная таблица носила название «список»)
Подробнее - во встроенной справке по объекту ListObject
Игорь, огромное Вам СПАСИБО за помощь. Дело в том, что я долгое время не могла решить проблему с CheckBox-ами в ячейках. Сама пробовала, инет перешерстила вдоль и поперек, даже на киберфоруме помощи просила. Ответ один - вставить чекбоксы в ячейку нельзя. А если и можно, то там к координате top привязка. А у меня "умная" или не очень :) динамическая таблица, которая заполняеся из БД Access динамически и мне нужно было выделять определенные строки и обрабатывать их прямо в коде. Вставлять в ячейку контролы и привязывать их руками нереально (так как нужно это в зависимости от количества строк в таблице БД динамически, а это только кодом) Вообщем хочу поблагодарить вас за идею ставить галочки в ячейке шрифтом - идеальный вариант для динамических таблиц. Только вот подсказали бы Вы мне, начинающей в области VBA, как работать с "умной таблицей" как с объектом VBA в коде, то бишь программно. Я просто не могу найти нигде информации: ищу то ли плохо, то ли все не там.
Спасибо, попробуем прикрутить
Благодарю за быстрый ответ. Дело в том что имеется таблица с большим кол-вом столбцов, которые необходимо скрывать перед печатью. Было бы удобно чтобы макрос скрывал столбцы если над ними поставить например галочку или определенный набор символов. Если на основе данного макроса это выполнить невозможно, будем дальше шерстить инет.
Смотря какой из трех вариантов вас интересует.
Автофильтр, к примеру, для скрытия столбцов не подойдёт.
Надо переделывать код - а что именно вам надо, я не знаю.
Обратитесь на форумы по Excel, прикрепите пример своего файла, - и вам обязательно помогут изменить код под ваши нужды.
Отличная вещь. А подскажите как сделать чтобы вместо строк скрывались столбцы?
Хотел написать довольно узкую статью про то, как автоматизировать скрытие ненужных и открытие, вдруг ставших нужными, строк/столбцов на листе Excel. Однако статья получилась несколько шире. Excel особенно хорошо демонстрирует свою силу, когда вы соединяете вместе несколько стандартных инструментов. Вот и я, придумывая пример, не удержался от этого "синтеза" и невольно пришлось выйти за ранее намеченные рамки статьи. Впрочем, я даже рад этому, так как вы только тогда расправите крылья в Excel, когда научитесь комбинировать свои отдельные навыки, как это показано в этой статье.
Синтез
Каждый приём, примененный в этом учебном примере, - банальщина, а сведенные воедино - уже некая магия. Какие же приёмы мы объединили:
Суть учебного примера
Будем формировать вот такой прямоугольник из звёздочек в рамке. Высотой и шириной которого, можно управлять при помощи двух счётчиков слева.
Разбор решения по шагам:
Смотрим лист Шаг 1 . Рисуем жирную рамку вокруг диапазона D2:AY23 . Зона для построения прямоугольника - E3:AX22 .
В ячейке A1 будем хранить ширину прямоугольника, который нам надо будет нарисовать звёздочками. В ячейке A2 - высоту.
На пересечении строки 25 и столбца BA намечаем единичками наш будущий пояс видимости . Что это такое, расскажу позже.
Создаём 2 именованных диапазона для A1 и A2 с названиями Ширина и Высота соответственно.
В ячейку E3 вводим формулу
=ЕСЛИ(И(СТОЛБЕЦ() или
=IF(AND(COLUMN() E3:AX22 .
Смотрим лист Шаг 2 . Меняем формат данных диапазона E3:AX22 на " ;;; ". Так же поступаем с A1:A2 . Этот пустой формат данных очень удобен тогда, когда наши ячейки содержат служебную вспомогательную информацию и мы не хотим визуализации на листе их значений.
Через ленту РАЗРАБОТЧИК при включенном Режиме конструктора , через кнопку Вставить , добавляем 2 элемента управления типа Счётчик . Изменяем их как показано на экранах. Данные счётчики будут использованы для изменения значения диапазонов Ширина и Высота .
Теперь разберемся с поясом видимости. Этот пояс будет обрабатываться макросом, который в чётком соответствии с ним будет скрывать или показывать соответствующие строки или столбцы. Например, если в F25 будет 1, то столбец F будет видимым, а если 0, то макрос скроет этот столбец. Соответственно, если BA5 будет содержать 1, то строка 5 будет видима, а если 0, то будет скрыта. Ячейка BA25 будет отвечать за видимость и строки 25 , и столбца BA одновременно.
В ячейки, чьи столбцы/строки, всегда должны быть видимыми (например A25:D25 , которые отвечают за столбцы A:D ) можно ввести просто единички. А вот в ячейки, которые должны управлять видимостью в динамике, мы введём формулы. E25 , например, будет содержать =ЕСЛИ(ИЛИ(СУММ(E3:E22)>0;Видимость);1;0) или =IF(OR(SUM(E3:E22)>0;Видимость);1;0). Для нашего удобства я ввёл ещё один именованный диапазон Видимость , который располагается на листе Настройки и позволяет для наших нужд сделать все строки/столбцы видимыми, что может потребоваться вам для внесения изменений на листе (в противном случае вам пришлось бы делать видимыми столбцы и строки вручную, что довольно неудобно). Если Видимость истинна, то все наши сигнальные ячейки пояса верности видимости становятся равными всегда 1, вне зависимости от значений ячеек диапазона E3:AX22 . В этом виновата формула ИЛИ (OR), которая возвращает ИСТИНА, если хотя бы 1 из её авгументов принял значение ИСТИНА.
Займёмся рисованием звёздочек на месте единичек в диапазоне E3:AX22 . Создадим правило условного форматирования, как показано на экранах.
Ну и сделаем простой макрос, который будет сканировать пояс видимости и если текущая видимость столбца/строки не соответствует поясу, то он приводит это в соответствие, то есть либо скрывает, либо показывает строку/столбец.
Макрос, оформленный в виде процедуры ShowHide , будем вызывать из событий изменения счётчиков
а также события активации листа
Сводим всё воедино
Итак, на итоговом листе Шаг 3 все компоненты собраны воедино. Элементы управления счётчики настроены на изменение именованных диапазонов Ширина и Высота , которые через формулы влияют на появление единичек в диапазоне E3:AX22 . На эти единички реагирует условное форматирование, рисуя на их месте звёздочки, и на них же реагируют ячейки пояса видимости, принимая значения 1 для тех столбцов/строк, которые должны быть видимыми, и 0 - для тех, что должны быть скрыты. Макрос, который скрывает/показывает строки и столбцы вызывается из события изменения счётчика и события активации листа.
Вывод
Я надеюсь, что не смотря на искусcтвенность примера, вы усвоили несколько важных приёмов, которые помогут вашим таблицам выглядеть более эффектно.
Читайте также: