Как в excel скрыть пустые строки

Как скрыть пустые строки? Программное скрытие пустых строк макросом VBA

Пустые строки в таблицах 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 или старше, то придется придумывать другие способы.

Как в excel скрывать несколько строк, если их ячейки либо, пустые либо =»»?

Как в excel скрывать несколько строк, их ячейки либо пустые либо =»» ?

нужно чтобы исчезали полностью строки и не печатались,
по самой последней ячейке из диапазона строк 5:29

т.е. сейчас должны стать скрытыми полностью строки 16:29
потому что последняя заполненная строка 15 (ячейка E15)
и чтобы строки 16:29 на принтере не печатались

т.е. должны быть автоматически скрыто (м.б. по умному фильтр настроить?)
Внимание! Ячейки диапазона строк 5:29 могут содержать формулы

• Вопрос задан 11 мар.
• 204 просмотра

Нашёл решение. Для каждого листа где нужно убирать строки, делаем следующее:
1. Сделать невидимые формулы справа от основного содержания листа, которые проверяют являются ли ячейки из столбцов b и e одновременно пустые, например, =ЕСЛИ(И(B19=»»;E19=»»); «»; 1) . И сделать фильтр по пустым ячейкам
2.создать макрос в каждом листе где есть фильтр, который автоматически обновляет фильтр:

В последнее время я сам нахожу ответы на свои вопросы.

Замечательно
то что вы написали равно

И не нужно ничего писать

На самом деле автоматизм тут сомнительный, т.к. для того что бы сработал макрос нужно перейти на другой лист и обратно, т.к. он сработает при активации листа. А если в книге лист один, то макрос сработает только при открытии книги. Получается что пользователю все равно нужно делать действие.

К тому же если пользователь отключит режим фильтрации, то макрос вызовет ошибку.

в дополнение,
что бы не вкладывать макрос в каждый лист можно записать его один раз в модуле книги, там есть это событие

Но для задачи такого автоматизма более чем достаточно, потому что данные берутся из 1 листа и распределяются по другим в которых макросы. Изменив что то в первом листе, нужно кликать чтоб посмотреть на изменения в другом там где фильтр и макрос.

Как сделать скрываемые строки или столбцы в Excel (Эксель)

Статья о том, как реализовать в файле программы «Эксель» строки или столбцы, скрывающиеся за ненадобностью.

Опишем пошагово три способа реализации, обозначенной выше задачи – спрятать строки или столбцы.

Предположим у нас имеется огромная таблица в тысячу столбцов шириной и 150 тыс. строк высотой. Для осмысления и аналитики информации, содержащейся в таблице гораздо проще и приятнее работать с промежуточными итогами, скрывая лишние строки.

Скрыть лишние строки можно следующими способами:

1. Первый способ — самый простой и примитивный заключается в том, чтобы выделить ненужные столбцы или строчки (сквозным выделением) и нажатием правой кнопки мыши в контекстном меню выбрать действие «Скрыть».
   Показать скрытые строки или столбцы можно выделив нужный диапазон и выбрав в контекстном меню действие «Показать».
2. Второй способ немного сложнее. Данный способ заключается в группировке строк или столбцов при помощи опции «Группировать» во вкладке «Данные» меню быстрого доступа. Эта опция позволяет разбить информацию на группы и открывать или прятать строки(столбцы) нажатием(кликом) по крестику. Преимущество этого метода в том, что можно создавать несколько уровней группировки и отображать при необходимость данные только определенного уровня. То есть информация открывается ступенчато в виде «древа».

Группировка данных осуществляется следующим образом:

• Необходимо выделить столбцы или строки сквозным выделением (для каждой группы отдельно);
• Кликнуть по пиктограмме «Группировка» в разделе «Данные». Информация будет сгруппирована автоматически.

Если Вам недостаточно автоматической группировки, можете использовать опцию «Создание структуры…».

Следует помнить, что группировка захватывает соседнюю с выделенным диапазоном ячейку, поэтому выделять нужно на одну ячейку меньше, либо следует вставить между группируемыми диапазонами пустые строки — разделители.

Третий способскрыть ненужные данные и отображать только важную информацию — это сводные таблицы. Сводные таблицы преображают весь диапазон информации в нужную Вам структуру данных. Важным преимуществом сводных таблиц является то, что вы можете конфигурировать структуру данных нужным вам способом в реальном времени в процессе проведения аналитики.
Процесс создания сводной таблице описан в статье ниже по ссылке:

Удаление пустых строк в Excel

Удаление пустых строк в Excel

Добрый день уважаемые читатели! Очень часто при работе с таблицами нам приходится удалять пустые строки. Когда таблица очень большая это становится реальной проблемой. Можно попробовать их скрыть, но тогда не получится видеть актуальную информацию (по адресам). Поэтому пойдём путём настоящих исследователей — создадим свою команду на удаление пустых строк. Предыдущая статья, посвящённая часам в ячейке — СТАТЬЯ.

На просторах интернета можно встретить ответ на поставленный вопрос, но, как правило, он сводится к одному:

1. На вкладке «Главная» нажимаем кнопку «Выделить»;
2. Выбираем «Пустые ячейки»;
3. Нажимаем «ОК»;
4. Нажимаем кнопку «Удалить».

Либо предлагается использовать фильтр. С помощью него выделить пустые строки, а затем их удалить.

Плюсом обоих способов является их «встроенность» в программу. Минусы — большое количество действий и, если таблица, большая, то сложно проверить результат. Но, как говорится, мы не ищем простых путей.

Как всегда напишем небольшой макрос. Его особенностью будет сам алгоритм нахождения пустых строк. Обычно макросы анализируют данные по строкам сверху вниз. Мы же заставим сделать наоборот — снизу вверх. Для того, чтобы не учитывать сдвиг удаляемых пустых строк. Таблица заготовка уже должна быть.

Вставим в наш документ новый модуль:

1. Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
2. Далее «Insert» — > «Module».

Далее вставим следующий код:

Dim intRow as Integer
Dim intLastRow as Integer
‘получение номера последней строки с данными
intLastRow = ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count — 1

‘Удалить пустые строки
For intRow = IntLastRow To 1 Step -1
If ActiveSheet.Rows(intRow).Text = «» Then
ActiveSheet.Rows(intRow).Delete
End If
Next intRow
End Sub

Немного пояснений. Первым делом объявляем две переменные — Row (строка) и LastRow (последняя строка). Тип целое.

Следующей строчкой мы показываем макросу найти последнюю строчку с данными на листе.

Далее «говорим» — в тех строчках где есть текст ничего не делать, где текста нет («») — эти строчки удалить. Вид таблицы до применения макроса.

Останется сохранить нашу книгу с поддержкой макросов (.xlsm) и выполнить макрос. Жмём на клавиатуре Alt+F8 и видим наш макрос (DeleteEmptyStrings). Щёлкаем «Выполнить»

Таблица примет следующий вид.

Видим, что вторая строка как будто пустая? Нет. В ней во второй ячейке я поставил пробел. Так что макрос правильно посчитал её не пустой и не удалил! Готово!

Ссылка на файл с макросом:

Если остались вопросы — смотрите наше новое видео!