Как скрыть пустые строки в excel автоматически
Пустые строки в таблицах Excel занимают место как на экране монитора, так и на листе бумаги и при этом не несут смысловой нагрузки. Тем не менее некоторые ячейки этих строк могут участвовать в расчетах, что делает их удаление неприемлемым. В таких случаях пустые строки можно скрыть, причем сделать это можно очень быстро.
Как скрыть пустые строки средствами Excel?
Скрыть строки вручную
Простейший способ заключается в том, чтобы зрительно отыскать пустые строки и выделить их. При выделении нескольких строк нужно удерживать клавишу Ctrl на клавиатуре. После этого необходимо навести курсор на выделенное поле, кликнуть по нему правой кнопкой мыши и выбрать пункт "Скрыть" из контекстного меню. Выделенные строки будут скрыты. Способ простой, но имеет свои минусы, можно пропустить строку, можно ошибочно выделить не ту строку.
Скрыть строки используя сортировку
Если предстоит скрыть большое количество строк, искать их, что называется "глазами" очень не удобно. В этом случае можно использовать сортировку для того чтобы пустые строки сгруппировались и после этого скрыть их, выделив сразу весь диапазон либо просто вывести за пределы печати при распечатывании документа. Минусом этого метода является то, что очередность строк нарушается.
Как скрыть пустые строки средствами VBA?
Программное скрытие макросом VBA
Теперь речь пойдет о том как программно скрыть пустые строки макросом. Возьмем процедуру, при помощи которой мы уже удаляли пустые строки и внесем в её программный код небольшие изменения. Заменим команду Rows(r).Delete на Rows(r).Hidden = True
Для того, чтобы перенести этот программный код на свой компьютер, наведите курсор мыши на поле с программным кодом , нажмите на одну из двух кнопкок в правом верхнем углу этого поля, скопируйте программный код и вставьте его в модуль проекта на своем компьютере (подробнее о том, как сохранить программный код макроса ).
Напомню, что область действия макроса - используемый диапазон, то есть все строки, находящиеся между первой и последней заполненными ячейками.
Автоматическое скрытие с использованием надстройки
Еще более гибкий инструмент для работы со строками - надстройка, сделанная на базе макроса 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 или старше, то придется придумывать другие способы.
Скрывайте или отображайте столбцы в электронной таблице, чтобы в ней отображались только нужные данные для просмотра или печати.
Скрытие столбцов
Выделите один или несколько столбцов и нажмите клавишу CTRL, чтобы выделить другие несмежные столбцы.
Щелкните выделенные столбцы правой кнопкой мыши и выберите команду Скрыть.
Примечание: Двойная линия между двумя столбцами свидетельствует о наличии скрытых столбцов.
Отображение скрытых столбцов
Выделите столбцы, смежные со скрытыми.
Щелкните выделенные столбцы правой кнопкой мыши и выберите команду Показать.
Можно также дважды щелкнуть двойную линию между двумя столбцами в том месте, где находятся скрытые столбцы.
Вы видите двойные линии в заголовках столбцов или строк, а не строки и столбцы, как показано на рисунке?
Эти двойные линии означают, что некоторые столбцы и строки скрыты. Чтобы отобразить скрытые данные, отобразите эти столбцы или строки. Вот как отобразить столбцы или строки вне зависимости от того, находятся данные в диапазоне или в таблице:
Выделите столбцы, находящиеся перед скрытыми столбцами и после них (например, столбцы C и F).
Щелкните правой кнопкой мыши выбранные заголовки столбцов и выберите команду Отобразить столбцы.
Ниже описано, как отобразить строки.
Выделите строки, находящиеся перед скрытыми строками и после них (строки 2 и 4 в примере).
Щелкните правой кнопкой мыши выбранные заголовки строк и выберите команду Отобразить строки.
Примечание: Если скрыты последовательные столбцы или строки, невозможно отобразить определенные из них. Сначала отобразите все столбцы или строки, а затем скройте те, которые не должны отображаться.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
При работе в программе Excel довольно часто можно встретить ситуацию, когда значительная часть массива листа используется просто для вычисления и не несет информационной нагрузки для пользователя. Такие данные только занимают место и отвлекают внимание. К тому же, если пользователь случайно нарушит их структуру, то это может произвести к нарушению всего цикла вычислений в документе. Поэтому такие строки или отдельные ячейки лучше вообще скрыть. Кроме того, можно спрятать те данные, которые просто временно не нужны, чтобы они не мешали. Давайте узнаем, какими способами это можно сделать.
Процедура скрытия
Спрятать ячейки в Экселе можно несколькими совершенно разными способами. Остановимся подробно на каждом из них, чтобы пользователь сам смог понять, в какой ситуации ему будет удобнее использовать конкретный вариант.
Способ 1: группировка
Одним из самых популярных способов скрыть элементы является их группировка.
- Выделяем строки листа, которые нужно сгруппировать, а потом спрятать. При этом не обязательно выделять всю строку, а можно отметить только по одной ячейке в группируемых строчках. Далее переходим во вкладку «Данные». В блоке «Структура», который располагается на ленте инструментов, жмем на кнопку «Группировать».
Способ 2: перетягивание ячеек
Самым интуитивно понятным способом скрыть содержимое ячеек, наверное, является перетягивание границ строк.
-
Устанавливаем курсор на вертикальной панели координат, где отмечены номера строк, на нижнюю границу той строчки, содержимое которой хотим спрятать. При этом курсор должен преобразоваться в значок в виде креста с двойным указателем, который направлен вверх и вниз. Затем зажимаем левую кнопку мыши и тянем указатель вверх, пока нижняя и верхняя границы строки не сомкнутся.
Способ 3: групповое скрытие ячеек перетягиванием
Если нужно таким методом скрыть сразу несколько элементов, то прежде их следует выделить.
-
Зажимаем левую кнопку мыши и выделяем на вертикальной панели координат группу тех строк, которые желаем скрыть.
Если диапазон большой, то выделить элементы можно следующим образом: кликаем левой кнопкой по номеру первой строчки массива на панели координат, затем зажимаем кнопку Shift и щелкаем по последнему номеру целевого диапазона.
Способ 4: контекстное меню
Два предыдущих способа, конечно, наиболее интуитивно понятны и простые в применении, но они все-таки не могут обеспечить полного скрытия ячеек. Всегда остается небольшое пространство, зацепившись за которое можно обратно расширить ячейку. Полностью скрыть строку имеется возможность при помощи контекстного меню.
- Выделяем строчки одним из трёх способов, о которых шла речь выше:
- исключительно при помощи мышки;
- с использованием клавиши Shift;
- с использованием клавиши Ctrl.
Способ 5: лента инструментов
Также скрыть строки можно, воспользовавшись кнопкой на ленте инструментов.
Способ 6: фильтрация
Для того, чтобы скрыть с листа содержимое, которое в ближайшее время не понадобится, чтобы оно не мешало, можно применить фильтрацию.
-
Выделяем всю таблицу или одну из ячеек в её шапке. Во вкладке «Главная» жмем на значок «Сортировка и фильтр», который расположен в блоке инструментов «Редактирование». Открывается список действий, где выбираем пункт «Фильтр».
Способ 7: скрытие ячеек
Теперь поговорим о том, как скрыть отдельные ячейки. Естественно их нельзя полностью убрать, как строчки или колонки, так как это разрушит структуру документа, но все-таки существует способ, если не полностью скрыть сами элементы, то спрятать их содержимое.
-
Выделяем одну или несколько ячеек, которые нужно спрятать. Кликаем по выделенному фрагменту правой кнопкой мыши. Открывается контекстное меню. Выбираем в нем пункт «Формат ячейки…».
Как видим, существует несколько разных способов, с помощью которых можно спрятать строки в Экселе. Причем большинство из них используют совершенно разные технологии: фильтрация, группировка, сдвиг границ ячеек. Поэтому пользователь имеет очень широкий выбор инструментов для решения поставленной задачи. Он может применить тот вариант, который считает более уместным в конкретной ситуации, а также более удобным и простым для себя. Кроме того, с помощью форматирования имеется возможность скрыть содержимое отдельных ячеек.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Как в excel скрывать несколько строк, их ячейки либо пустые либо ="" ?
нужно чтобы исчезали полностью строки и не печатались,
по самой последней ячейке из диапазона строк 5:29
т.е. сейчас должны стать скрытыми полностью строки 16:29
потому что последняя заполненная строка 15 (ячейка E15)
и чтобы строки 16:29 на принтере не печатались
т.е. должны быть автоматически скрыто (м.б. по умному фильтр настроить?)
Внимание! Ячейки диапазона строк 5:29 могут содержать формулы
Нашёл решение. Для каждого листа где нужно убирать строки, делаем следующее:
1. Сделать невидимые формулы справа от основного содержания листа, которые проверяют являются ли ячейки из столбцов b и e одновременно пустые, например, =ЕСЛИ(И(B19="";E19=""); ""; 1) . И сделать фильтр по пустым ячейкам
2.создать макрос в каждом листе где есть фильтр, который автоматически обновляет фильтр:
В последнее время я сам нахожу ответы на свои вопросы.
Замечательно
то что вы написали равно
И не нужно ничего писать
Макрос делает автоматическое обновление, а ваш вариант ручной.
Текст вопроса читайте, нужно сделать автоматическое обновление без вмешательства пользователя.
На самом деле автоматизм тут сомнительный, т.к. для того что бы сработал макрос нужно перейти на другой лист и обратно, т.к. он сработает при активации листа. А если в книге лист один, то макрос сработает только при открытии книги. Получается что пользователю все равно нужно делать действие.
К тому же если пользователь отключит режим фильтрации, то макрос вызовет ошибку.
в дополнение,
что бы не вкладывать макрос в каждый лист можно записать его один раз в модуле книги, там есть это событие
Но для задачи такого автоматизма более чем достаточно, потому что данные берутся из 1 листа и распределяются по другим в которых макросы. Изменив что то в первом листе, нужно кликать чтоб посмотреть на изменения в другом там где фильтр и макрос.
Читайте также: