Как удалить строки в Excel по условию? Удаление и скрытие пустых строк и строк, содержащих заданное значение
Прежде чем перейти к теме статьи, разберемся, с чем мы будем иметь дело. Посмотрим на иерархию объектов в Excel. Рабочая книга (Workbook) – это файл Excel, содержащий рабочие листы. Рабочий лист (Worksheet), в свою очередь, представляет собой электронную таблицу, основной тип документа, который используется приложением Excel для хранения и обработки данных. Рабочие листы — это таблицы, состоящие из ячеек, организованных в столбцы и строки и являются частью рабочей книги. Основным элементом электронной таблицы является ячейка. Именно в ячейках содержится информация – текст, значения, формулы. Каждая ячейка имеет свой адрес – номер строки и номер столбца, на пересечении которых находится ячейка. В этой статье более подробно остановимся на строках.
Строки в Excel и основные действия с ними
Количество строк в Excel 2003 – 65 536, а число строк в Excel 2007 и Excel 2010 – уже 1 048 576. Наиболее частыми действиями со строками являются копирование, вставка, добавление новых строк, удаление и скрытие. Без особого труда можно преобразовать Excel строку в столбец и наоборот. Такое преобразование называется транспонированием . Для преобразования строки в столбец необходимо скопировать данные строки, после чего нажать правую кнопку мыши и из контекстного меню выбрать пункт «Специальная вставка…». В появившемся диалоговом окне «Специальная вставка» поставить галочку в поле «транспонировать» и нажать кнопку ОК.
Все манипуляции со строками не представляют какой-либо особой сложности, но задача существенно усложняется, когда работать приходится с большими объемами данных и в особых условиях, когда к примеру нельзя воспользоваться стандартными средствами Excel, такими как сортировка. В таких случаях на помощь как всегда приходят макросы VBA. С помощью макросов VBA Excel строки легко поддаются обработке, можно например скрыть все пустые строк и или удалить строки содержащие заданный текст.
Далее рассмотрим инструмент, в котором реализовано программное скрытие и удаление пустых строк, а также скрытие и удаление любых строк, по заданным пользователем условиям, при этом все параметры для работы программы задаются не в редакторе программного кода, а в диалоговом окне, что очень удобно. Надстройка работает как с видимыми так и со скрытыми и отфильтрованными строками .
Надстройка для удаления и скрытия строк по условию
1) Скрывать либо удалять строки, подходящие под заданное условие;
2) скрывать либо удалять строки, находящиеся ниже или выше строк, подходящих под заданное условие;
3) скрывать либо удалять заданное количество строк для каждого действия;
4) скрывать либо удалять разное количество строк для каждого действия, если количество строк указано в отдельном столбце;
5) выбирать одно из нескольких условий для искомых значений (совпадает, не совпадает, содержит, не содержит, начинается, не начинается, заканчивается, не заканчивается);
6) находить пустые строки и строки, содержащие заданный текст (либо массив из текстовых значений);
7) осуществлять одновременный поиск различных текстовых выражений, внесенных в поле для ввода текста с использованием знака-разделителя. В качестве знака-разделителя используется (;) точка с запятой;
8) выбирать различные диапазоны для обработки данных;
9) ограничивать выбранные диапазоны номерами строк сверху и/или снизу для избежания повреждения шапок таблиц и строк с разного рода промежуточными итогами;
10) осуществлять поиск текста в строках как с учетом регистра, так и без.
Диалоговое окно надстройки
По изображению диалогового окна можно получить представление о том, какие возможности предоставляются надстройкой — это удаление/скрытие пустых строк и строк, подходящих под заданные условия.
Как скрыть или удалить строки по условию?
На выбор пользователя макрос может удалять/скрывать пустые строки, а может на выбор пользователя скрывать либо удалять строки, удовлетворяющие заданным условиям, например таким как содержание заданного текста, либо отсутствием такового, может учитывать регистр при поиске текста в строках, а может не учитывать. Для того, чтобы при удалении/скрытии в таблицах не пострадала шапка или промежуточные результаты, введено дополнительное ограничение по номерам строк, при помощи которого можно задавать диапазон для действия макроса. Если же диапазон не задан пользователем, то макрос работает со строками так называемого используемого диапазона, то есть со строки, в которой содержится первая заполненная информацией ячейка, до строки с последней заполненной ячейкой. Пользователю также предлагается ограничить обработку данных либо активным листом, либо всеми листами активной рабочей книги.
При помощи надстройки можно производить действия не только с самими строками, которые подходят под заданное условие, но и со строками, которые находятся выше или ниже этих строк. Кроме того, предусмотрена возможность задать количество строк для каждого действия.
Как скрывать или удалять разное количество строк?
Если в диалоговом окне надстройки установить флажок в поле "Разное количество строк", то числовое значение в поле "Для каждого действия, шт." меняется на номер столбца, в котором содержатся значения для каждого действия. В этом случае за каждое действие будет скрыто или удалено столько строк, сколько указано в соответствующей строке заданного столбца.
Как скрывать или удалять строки со служебными символами?
При необходимости удаления или скрытия строк со служебными символами, такими как звездочка или знак вопроса, в строке поиска необходимо перед искомым знаком поставить символ "
" тильда, это сигнализирует приложению Excel о том, что следующий за тильдой знак перестает выполнять свои служебные функции и становится обычным символом.
Удаление (скрытие) строк по условию
макрос удалит на листе все строки, в которых содержится искомый текст:
Чтобы вместо удаления просто скрыть такие строки, замените строку
Расширенная версия этого макроса — с использованием UserForm для ввода искомого значения
Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:
| ConditionalRowsDeleting.xls | 24 КБ |
| ConditionalRowsDeletingUsingUserform.xls | 45.5 КБ |
- 216200 просмотров
Комментарии
Я не занимаюсь поддержкой бесплатных макросов, и модификацией их под ваши требования.
В комментариях к статье есть очень много вариантов переделки этого макроса, — полистайте комменты, может найдете подходящий вариант кода.
Если же хотите получить готовое решение, — оформляйте заказ на сайте, сделаю (платно. минимальная стоимость заказа 1500 рублей)
Добавление комментариев к данной статье на этом отключаю.
Подскажите пожалуйста. Мучаюсь уже неделю. Не могу переделать существующие макросы для скрытия строк. Имеется таблица с цифрами. Нужно что бы скрывались строки если одновременно в столбце А и В и С и D значение меньше чем 0. Было бы не плохо это значение вводить в user form и там были кнопочки скрыть и показать все. Помогите пожалуйста. Да и ещё количество строк может бить разное. А цифровые значения начинаются с второй строки.
Здравствуйте, у меня такая ситуация. Есть таблица, с 3000 строками, и примерно 25 столбиками
Первые 10 столбиков заполнены текстом, с 11 по 25 столбик бывают цифры. Как мне удалить полностью строку, если в столбики с 11 по 25 все пустые ячейки ? а если хоть одна ячейка заполнена, то строку не трогать? но на первые десять столбиков не надо обращать внимание
А как сделать такое же условие для объеденных ячеек только по нескольким значениям?
Я должен был догадаться, какие листы надо обрабатывать, а какие нет?
Чтобы обработать ВСЕ листы, — напишите
Большое спасибо, но у меня следующие листы "3", "3А", "3Б", "4".
Подскажите, как сделать этот макрос для листов "2", "2А", "2Б" и т. д.
Sub УдалениеСтрокПоУсловию()
Dim ra As Range, delra As Range, ТекстДляПоиска As String
Application.ScreenUpdating = False ‘ отключаем обновление экрана
ТекстДляПоиска = "Наименование ценности" ‘ удаляем строки с таким текстом
‘ перебираем все строки в используемом диапазоне листа
For Each ra In ActiveSheet.UsedRange.Rows
‘ если в строке найден искомый текст
If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then
‘ добавляем строку в диапазон для удаления
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
End If
Next
‘ если подходящие строки найдены — удаляем их
If Not delra Is Nothing Then delra.EntireRow.Delete
End Sub
Если необходимо наоборот отобразить строки "EntireRow.Hidden = False"
То как осуществить поиск текста в скрытых строках/столбцах?
Здравствуйте. Подскажите, пожалуйста, как прописать макрос так, чтоб он:
1. удалял все строки с заливкой цветом, или
2. удалял все строки, содержащие какую либо ячейку с заливкой цветом.
Спасибо.
Игорь,благодарю за макрос — облегчает жизнь! Вы не посоветуете как работать с этим макросом при защите листа?
Добрый день! Подскажите пожалуйста, Как сделать макрос скрывающий строки при условии что в двух подряд столбцах 0 или пусто? и чтоб он запускался не при открытии файла, а кнопкой?
у меня вариант с условием одного столбца, как его исправить
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim d As Integer
d = UsedRange.Rows.Count + 1
For rwIndex = 1 To 31
colIndex = 3
If Cells(rwIndex, colIndex).Value = 0 Then
Rows(rwIndex).Hidden = True
End If
Next
End Sub
Буду очень признательна)
Добрый день! У меня проблема с большими таблицами и с их колличеством) В одной книге у меня 25 листов на каждом большие таблицы, для удбства печати я создала макрос скрывающий ненужные строки, столбцы и ячейки во всей книге сразу, поочередно на каждом листе. Теперь хочу чтоб можно было сразу во всей книге развернуть скрытые ячейки. И не могу) Подскажите как исправить этот макрос чтоб он работал в модуле "Эта книга" для всех листов сразу
Sub Show()
Columns.Hidden = False
Rows.Hidden = False
Во — то что доктор прописал — спасибо! И извините за не корректность:)
Сандер, я написал код так, как вы просили, — если в ячейке присутствует цифра, то строка удаляется.
А вам надо было удалять только ячейки с ЧИСЛОМ (а не содержащие цифры)
Замените
Увы. работает аналогично с "Удаление (скрытие) строк по условию", т.е. если в тексте присутствует допустим "Адаптер АТ-2000 14 В22" — что там, что в вашем примере удаляет строку. Просто думал — есть что-нить персональное с цифрами.
Сандер, так попробуйте:
Здравствуйте! Пролистал, прочитал — таки не нашел ответ к своей задаче, а она такова: в столбце чередуются и текст и цифры — надо удалить строки которые содержат цифры(они естно — разные). Спасибо!
Евгений, при работе макросов, отмена действий в Excel не работает
(что сделано макросом, никак не отменить, — так устроен Excel)
Подскажите. после удаления строк с помощью макроса, нельзя почему то вернуть назад назад изменения.Что нужно сделать чтоб вернуть изменения назад.
Здравствуйте, Нурьяна.
Моя программа нормально работает, — если ей правильно настроить
(настройка под каждый конкретный сайт, — от 1500 руб, + сама программа 2500 стоит)
PS: на будущее, размещайте коммент не в первой попавшейся статье, а в статье с описанием программы-парсера
http://excelvba.ru/programmes/Parser
можете сразу оформить заказ на парсер, в таком виде:
http://excelvba.ru/programmes/Parser/order
Здравствуй Админ! Долго Вас искала. подрабатываю на СП закупках, и уже замучилась с заполнением каталогов, сил больше нет. Как-то покупала граббер у одного программиста, ну вообще не довольна , на одну закупку граббер стоит 2800, и работает через раз. Скажите как работает Ваша программа и сколько стоит, и можно ее настроить под сайт на котором я работаю и пользоваться ею постоянно один раз заплатив?
Можем сделать под заказ, — всё будет работать как надо.
Хоть убейте — не работает. Идея в том, что макрос срабатывает при нажатии на ячейку. Берет значение из текущей активной строки и 2 столбца, ищет на другом листе и должен его просто выделить. Вся идея.
В коде грешу на
"ВзятьДанные = Cells(ActiveCell.Row, 2).Value"
Он не берет значение -> не может найти его на другом листе. Как заставить его видеть значение. ( Кавычки ставил — не помогает.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ra As Range, finra As Range
If Target.Cells.Value = "" Then Exit Sub
If Not Intersect(Target, Range("AF3:AF5000")) Is Nothing Then
ВзятьДанные = Cells(ActiveCell.Row, 2).Value
Sheets("Результат").Select
For Each cell In Range("A3:A2000").Cells
If cell = ВзятьДанные Then
If finra Is Nothing Then Set finra = cell Else Set finra = Union(finra, cell)
End If
Next
If Not finra Is Nothing Then finra.EntireRow.Select
Application.ScreenUpdating = True
End If
End Sub
Андрей, так попробуйте
Со скрытием строчек все отлично. но что делать, если в дальнейшем мне нужно некоторые скрытые строки вновь раскрыть (т.к. признак для скрытия для них может в будущем быть нерелевантен)?
Сделал такую билеберду, но скрытые строки он не раскрывает 🙁 Видимо не видит скрытые строки. Помогите советом пожалуйста!
Sub Макрос()
Dim ra As Range, delra As Range, ТекстДляПоиска As String
Application.ScreenUpdating = False
For Each ra In ActiveSheet.UsedRange.Rows
If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
End If
Next
If Not delra Is Nothing Then delra.EntireRow.Hidden = False
End Sub
Кирилл, так попробуйте:
УдалятьСтрокиСТекстом = Worksheets("ИмяВторогоЛиста").range("a2:a10")
или так
УдалятьСтрокиСТекстом = Worksheets(2).range("a2:a10")
Добрый день! Ваш макрос очень выручает, Подскажите, как сделать, чтобы слова для поиска брались не из строки = Array("Наименование *", "Количество", "текст?", "цен*сти", "*78*"),а с другого листа
Спасибо, заработало.
Ну я и тупой.
Sub погрузка()
Dim ra As Range, delra As Range
Application.ScreenUpdating = False ‘ отключаем обновление экрана
‘ ищем и удаляем строки, содержащие заданный текст
‘ (можно указать сколько угодно значений и использовать подстановочные знаки)
УдалятьСтрокиСТекстом = Array("ИД пункта:", "ИД маршрута:", _
"Название модели:", "Склад отгрузки:")
‘ перебираем все строки в используемом диапозоне листа
For Each ra In ActiveSheet.UsedRange.Rows
‘ перебираем все фразы в массиве
If ra.Row >= 17 Then
For Each word In УдалятьСтрокиСТекстом
Next word
End If
‘ если в очередной строке листа найден искомый текст
If Not ra.Find(word, , xlValues, xlPart) Is Nothing Then
‘ добовляем строку в диапазон для удаления
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
End If
Next
‘ если подходящие строки найдены, то (оставте одну из следующих строк)
If Not delra Is Nothing Then delra.EntireRow.Hidden = True ‘ скрываем их
If Not delra Is Nothing Then delra.EntireRow.Delete ‘ удаляем их
End Sub
Пожалуйста, подскажите что не так, в таком исполнении не хочет удалять с 17 строки.
Gjlcrf;bnt xnj yt nfr
If ra.row >= 17 then
For Each word In УдалятьСтрокиСТекстом
.
Next word
end if
Похоже я не совсем правильно выразил свою мысль, в макросе УдалениеСтрокПоНесколькимУсловиям нужно удалять строки с определенным текстом только с 17 строки и до конца листа.
Вместо
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
пишете
ra.Replace ("Что заменить", "")
нет, мне в этом макросе нужно что бы текст удалялся с определенной сторки.
Судя по описанию задачи, Вам нужна штатная функция Эксель — заменить. Ctrl+H. Найти — пишете нужный Вам текст, поле "Заменить на" оставляете пустым.
а можете подсказать, как можно найти и удалить текст с определенной строки.