Excel скрыть строки по условию

Обновлено: 06.07.2024

Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:

Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.

Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,

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

В реальной жизни примеров таких таблиц - море.

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

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

Для начала давайте создадим пользовательский стиль ячейки, применение которого будет скрывать ее содержимое с помощью особого формата. На вкладке Главная (Home) в списке стилей найдите стиль Обычный (Normal), щелкните по нему правой кнопкой мыши и выберите команду Дублировать (Duplicate):

В появившемся после этого окне введите любое имя стиля (например Секрет), снимите все галочки кроме первой (чтобы стиль не менял остальные параметры ячейки) и нажмите кнопку Формат (Format):

На вкладке Число (Number) выберите вариант Все форматы (Custom) и введите в поле Тип (Type) три точки с запятой подряд без пробелов:

Закройте все окна нажатием на ОК. Мы только что создали пользовательский формат, применение которого будет скрывать содержимое выделенных ячеек и увидеть его можно будет только в строке формул при выделении каждой отдельной ячейки:

Как это работает на самом деле

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

Первый - если в ячейке число больше нуля
Второй - если меньше
Третий - если в ячейке ноль
Четвертый - если в ячейке текст

Три подряд точки с запятой Excel воспринимает как четыре пустых маски для всех четырех возможных случаев, т.е. выводит пустоту при любом значении ячейки.

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

Чтобы вместо удаления просто скрыть такие строки, замените строку

Расширенная версия этого макроса - с использованием UserForm для ввода искомого значения

Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:

Не знаю, что вы там делаете, но строка "If ws.Index = 1 Then" явно не из моего макроса
(в моем макросе нет переменной ws)
Вот если вы показали бы полный код своего макроса - тогда можно было бы сказать, в чем именно ошибка.

У меня не удаляет ничего( Что я делаю не правильно?
удалять только на первом листе, который называется "накладная"
If ws.Index = 1 Then
Правильно?

Спасибо. Все работает как нужно - с Вашей помощью.

А вы попробуйте сначала удалить нули из ячеек

А потом удаляйте строки с пустыми ячейками:

Просмотрел много форумов по удалению строк через макрос. Нашел один который частично подходит мне, он удаляет строки если в 5 столбце пусто во всех листах кроме первого. Но не знаю как добавить, чтоб удалял еще и строки если в 4 столбце значение 0. Код быстрый (без цикла) а как дополнить - ума не приложу.
Sub DeleteEmptyRowsToAll()
Application.ScreenUpdating = False ' отключаем обновление экрана
On Error Resume Next
Dim ws As Worksheet 'декларирование переменой
For Each ws In Worksheets
If ws.Index > 1 Then ' кроме первого листа
ws.Columns(5).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ' удаляем сразу все строки, в которых в 5-м столбце - пусто
End If
Next
End Sub

Огромное спасибо за помощь. :)

Про чёрные ячейки в столбце.
Файл обновляется несколько раз в день - делать выборку вручную не вариант, нужен макрос.
А офис 2007 покупать шеф не хочет - 18 компов. Хватит вам и 2003, тем более официальный.
Про форум - пардон, не так назвал. Извините, если обидел.

Используйте фильтр по цвету (он доступен в Excel 2007 и выше): скройте все чёрные ячейки в столбце, после чего удалите видимые строки, и отключите фильтрацию.
Всё быстро и просто - и никаких макросов.

PS: Про какой форум речь? У меня на сайте форума нет, и не предвидится в ближайшем будущем.

Очень хороший форум! Часто им пользуюсь, а вот теперь забуксовал.
задача: имеется таблица, в которой, например, третий из столбец заполнен по принципу - или черная или белая ячейка. Нужно удалить строки с белыми ячейками в этом столбце. Заранее спасибо!

Вовсе необязательно вкладывать 3 условия в макрос.
Я бы на вашем месте в дополнительные столбец поместил формулу типа такой:
=ЕСЛИ(ИЛИ(Q1<5;НЕ(ЕОШ(НАЙТИ("текст";I1;1)));И(СЧЁТЕСЛИ(L:L;L1)=1;Q1>2));"";"не удаляем")

И потом бы макросом (или через автофильтр) удалил пустые строки по доп.столбцу

Добрый день, подскажите как написать макрос для скрытия и открытия (Или удаления) строк таблицы если в диапазоне ячеек [Пример:(d5:g100)] строка содержит в ячейках нули (или формулу, но отображает по формуле ноль)

Это как раз то что я так долго искал! Большое спасибо за информацию!

Помогите пожалуйста решить чуть более сложную задачу.
Есть несколько условий для удаления,
1) если значение ячейки (числовое) в столбце "Q" меньше "число" - удаляем строку
2) если значение ячейки (текстовое) в столбце "I" содержит "текст" - удаляем строку
И самое, наверное сложное:
3) если значение ячейки (текстовое) в столбце "L" встречается на листе только 1 раз и при этом значение ячейки (числовое) в столбце "Q" больше "число" - удаляем строку

И как все эти условия правильно в циклы вложить
PS В файле больше 30000 строк

Заранее спасибо! С уважением!

Большое спасибо за помощь! Работает!

Чтобы обработка ячеек начиналась с 15-строки, надо изменить анализируемый диапазон:

Т.е. поиск будет производится на пересечении используемого диапазона листа, и диапазона строк с 15-й по последнюю строку листа
(таким образом мы отсекаем первые 14 строк)

Кир Булычев, причину такого поведения вашего макроса я не знаю.
Надо смотреть файл - возможно, проблема в нём.
И, кстати, для 10000 строк надо использовать другие, более правильные и быстрые, алгоритмы
(хотя бы потому, что Union работает медленно, и не может вмещать в себя более 1000 ячеек. Т.е. если у вас с нулями и единицами будет 1500 строк, - то удалится только около 1000 строк, остальные макрос пропустит)

PS: За помощью лучше обращайтесь на форумы по Excel
(я помогаю не бесплатно)

Вот пользовался макросом который Вы посоветовали (я заменил только локацию данных):

For Each cell In Range("O:O").SpecialCells(xlCellTypeConstants)
If Val(cell) = 0 Or Val(cell) = 1 Then
If delra Is Nothing Then Set delra = cell Else Set delra = Union(delra, cell)
End If
Next cell
If Not delra Is Nothing Then Intersect(delra.EntireRow, Range("M:O")).Delete
Intersect(Range("O:O").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow, Range("M:O")).Delete

- но вот недавно, увеличилось число обрабатываемых данных (со 180 до 380 строк, а будет и до 10 000) и если раньше макрос удалял данные в строках с "М" по "О" если в "О" содержатся "1", "0" или "N/A" и поднимал их вверх (т.е. удалял строки) - то теперь он также удаляет данные НО стирает не строки а 2 соседних столбца справа. При возврате к меньшему кол-ву обрабатываемых ячеек/данных - всё снова работает нормально.
Решил опять к Вам обратится за помощью т.к. составные этого макроса к сожалению находятся за граню моих познаний в VBA.

Буду очень благодарен если посоветуете что-нибудь.

Скажите, пжта, если надо начать рассматривать массив начиная допустим с 15-й строки. Какие изменения претерпит макрос?

Здравствуйте !
У меня вопрос по макросу "позволяющему выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:"

Мне нужно чтоб удалялись строки со значением НОЛЬ.
Ставлю "0" и удаляются все значения в значении которого есть НОЛЬ.

Как сделать что бы такого не происходила! Сохранив при этом удаление по нескольким условиям.

А можно как-то сделать так, чтобы этот макрос всегда был включен при заходе в файл Exela и работал динамически. К примеру, если поменяются данные в ячейках C2:CI2, то сразу скроются или отобразяться ячейки согласно макросу.

Вы правы. Спасибо большое, он работает хорошо! У меня неполучилось потому что я буквы ставил в ячейки, если подставить цифры то все будет работать правильно :)

Неужели? Плохо проверяли.
Этому макросу нет разницы, что в ячейках - формулы или значения.

Sub СкрываемПустыеСтолбцы()
Dim cell As Range: Application.ScreenUpdating = False
For Each cell In [c2:ci2].Cells
' скрываем столбцы, если в ячейке ноль или пусто
cell.EntireColumn.Hidden = Val(cell) = 0
Next cell
End Sub

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

Моя задача решена. Спасибо огромное))) В целом - действовала по предложенному Вами плану))) Спасибо!)))))

В макросе в выражении ra.Find(word, , xlValues , xlPart) есть опция xlValues - она отвечает за поиск В ЗНАЧЕНИЯХ.
Есть и другие варианты этой опции:
xlFormulas - поиск в формулах
xlComments - поиск в комментариях к ячейке

Как бы для меня очень затруднительным получается п.3. ((((

Т.е. создать бегунок по строкам (нахожу совпадение - копирую) нереально?(((((

Здравствуйте, Ольга.
Тут многое зависит от того, насколько "огромный" ваш файл (тысячи, десятки или сотни тысяч, миллион строк?)
При разных объёмах данных - разные алгоритмы.

Представленный мной код корректно работает для небольших таблиц (где число скрываемых\удаляемых строк не превышает тысячи)
Это ограничение легко обойти, но для увеличения производительности необходимо полностью изменить алгоритм макроса
(что моментально работает на тысячах строк, может нещадно тормозить на миллионе строк)

Самый простой способ, который приходит мне на ум - с использованием формулы в доп.столбце:
1) на отдельном листе делаем список из 50 позиций
2) в дополнительном (пустом) столбце с огромной таблицей пишем формулу, которая проверяет наличие одного из 50 слов в строке
3) применяем автофильтр в этому доп. столбцу, оставляя видимыми лишь те строки, для которых формула вернула результат "совпадение найдено"
4) выделяем и копируем видимые строки на другой лист

Подскажите, пожалуйста, как можно решить такую задачу:
В документе Excel нужно организовать поиск: сразу искать по 50 позиций (огромный файл нужен быстрый поиск по огромному кол-ву материалов)и выводить на новый лист уже найденные позиции и все, что соответствует этим позициям в строке (например, найти среди строительных материалов песок, гравий и т.д. и вывести вместе с количеством на новый лист). Даже не знаю, как лучше эти 50 позиций для поиска делать: лучше бы через UserForm, но пока не получается((((

Буду признательна за любую помощь.
Спасибо)

Вам поможет такой макрос:
(не самый быстрый - но экономить миллисекунды не вижу смысла)

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

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

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

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

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

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

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

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

Пычев Анатолий,
Макрос делает автоматическое обновление, а ваш вариант ручной.
Текст вопроса читайте, нужно сделать автоматическое обновление без вмешательства пользователя.

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

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

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

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