Удаление (скрытие) строк по условию

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

(пример - во вложении ConditionalRowsDeleting.xls)

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

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

If Not delra Is Nothing Then delra.EntireRow.Delete

на
If Not delra Is Nothing Then delra.EntireRow.Hidden=TRUE


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

(пример - в файле ConditionalRowsDeletingUsingUserform.xls)


Function ПоискСтрокПоУсловию(ByVal ТекстДляПоиска As String, Optional HideOnly As Boolean) As Long
    ' функция получает в качестве параметра ТекстДляПоиска (можно использовать символы * и ?)
    ' Если HideOnly = TRUE, то строки, содержащие в ячейках ТекстДляПоиска, скрываются,
    ' иначе (HideOnly = FALSE - по умолчанию) - удаляются
    ' Функция возвращает количество удалённых строк
    Dim ra As Range, delra As Range
    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
    On Error Resume Next: ПоискСтрокПоУсловию = delra.Areas.Count ' количество найденных строк
    
    If Not delra Is Nothing Then    ' если подходящие строки найдены - скрываем или удаляем их
        If HideOnly Then delra.EntireRow.Hidden = True Else delra.EntireRow.Delete
    End If
End Function


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

Sub УдалениеСтрокПоНесколькимУсловиям()
    Dim ra As Range, delra As Range
    Application.ScreenUpdating = False    ' отключаем обновление экрана

    ' ищем и удаляем строки, содержащие заданный текст
    ' (можно указать сколько угодно значений, и использовать подстановочные знаки)
    УдалятьСтрокиСТекстом = Array("Наименование *", "Количество", _
                                  "текст?", "цен*сти", "*78*")
 
    ' перебираем все строки в используемом диапазоне листа
    For Each ra In ActiveSheet.UsedRange.Rows
        ' перебираем все фразы в массиве
        For Each word In УдалятьСтрокиСТекстом
            ' если в очередной строке листа найден искомый текст
            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 word
    Next
 
    ' если подходящие строки найдены, то: (оставьте одну из 2 следующих строк)
    If Not delra Is Nothing Then delra.EntireRow.Hidden = True    ' скрываем их
    If Not delra Is Nothing Then delra.EntireRow.Delete    ' удаляем их
End Sub

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

Я не занимаюсь поддержкой бесплатных макросов, и модификацией их под ваши требования.

В комментариях к статье есть очень много вариантов переделки этого макроса, - полистайте комменты, может найдете подходящий вариант кода.
Если же хотите получить готовое решение, - оформляйте заказ на сайте, сделаю (платно. минимальная стоимость заказа 1500 рублей)

Добавление комментариев к данной статье на этом отключаю.

Подскажите пожалуйста. Мучаюсь уже неделю. Не могу переделать существующие макросы для скрытия строк. Имеется таблица с цифрами. Нужно что бы скрывались строки если одновременно в столбце А и В и С и D значение меньше чем 0. Было бы не плохо это значение вводить в user form и там были кнопочки скрыть и показать все. Помогите пожалуйста. Да и ещё количество строк может бить разное. А цифровые значения начинаются с второй строки.

Здравствуйте, у меня такая ситуация. Есть таблица, с 3000 строками, и примерно 25 столбиками
Первые 10 столбиков заполнены текстом, с 11 по 25 столбик бывают цифры. Как мне удалить полностью строку, если в столбики с 11 по 25 все пустые ячейки ? а если хоть одна ячейка заполнена, то строку не трогать? но на первые десять столбиков не надо обращать внимание

А как сделать такое же условие для объеденных ячеек только по нескольким значениям?

Спасибо

Я должен был догадаться, какие листы надо обрабатывать, а какие нет?
Чтобы обработать ВСЕ листы, - напишите

 If sh.Name Like "*" Then

Если надо обработать только листы, название которых начинается с цифры, - то так:
 If sh.Name Like "#*" Then

Большое спасибо, но у меня следующие листы "3", "3А", "3Б", "4"...

Sub ДляНесколькихЛистов()
    Dim ra As Range, delra As Range, ТекстДляПоиска As String
    Application.ScreenUpdating = False        ' отключаем обновление экрана

    ТекстДляПоиска = "Наименование ценности"        ' удаляем строки с таким текстом

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name Like "2*" Then        ' если имя листа начинается с цифры 2
            ' перебираем все строки в используемом диапазоне листа
            For Each ra In sh.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 If
    Next sh
End Sub

Подскажите, как сделать этот макрос для листов "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

Буду очень признательна)

Sub Show()
    Dim sh As Worksheet: Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets ' перебираем все листы
        sh.Columns.Hidden = False
        sh.Rows.Hidden = False
    Next sh
    Application.ScreenUpdating = True
End Sub

Добрый день! У меня проблема с большими таблицами и с их колличеством) В одной книге у меня 25 листов на каждом большие таблицы, для удбства печати я создала макрос скрывающий ненужные строки, столбцы и ячейки во всей книге сразу, поочередно на каждом листе. Теперь хочу чтоб можно было сразу во всей книге развернуть скрытые ячейки. И не могу) Подскажите как исправить этот макрос чтоб он работал в модуле "Эта книга" для всех листов сразу

Sub Show()
Columns.Hidden = False
Rows.Hidden = False

End Sub

Спасибо)

Во - то что доктор прописал - спасибо! И извините за не корректность:)

Сандер, я написал код так, как вы просили, - если в ячейке присутствует цифра, то строка удаляется.
А вам надо было удалять только ячейки с ЧИСЛОМ (а не содержащие цифры)
Замените

If cell like "*#*" Then ' если ячейка содержит хоть одну цифру - удаляем строку

на
If isnumeric(cell) Then ' если в ячейке числовое значение - то удаляем

Увы... работает аналогично с "Удаление (скрытие) строк по условию", т.е. если в тексте присутствует допустим "Адаптер АТ-2000 14 В22" - что там, что в вашем примере удаляет строку... Просто думал - есть что-нить персональное с цифрами.

Сандер, так попробуйте:

Sub ПоискЦифр() ' перебор ячеек диапазона g1:g30 в поисках цифр
    Dim cell As Range, delra As Range
    Application.ScreenUpdating = False
 
    For Each cell In Range("g1:g30").Cells
        If cell like "*#*" Then ' если ячейка содержит хоть одну цифру - удаляем строку
            If delra Is Nothing Then Set delra = cell Else Set delra = Union(delra, cell)
        End If
    Next
 
    If Not delra Is Nothing Then delra.EntireRow.Delete 
    Application.ScreenUpdating = True
End Sub

Здравствуйте! Пролистал, прочитал - таки не нашел ответ к своей задаче, а она такова: в столбце чередуются и текст и цифры - надо удалить строки которые содержат цифры(они естно - разные). Спасибо!

Евгений, при работе макросов, отмена действий в 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 Макрос2() ' перебор ячеек диапазона g1:g30 в поисках значения "da"
    Dim cell As Range, delra As Range, ТекстДляПоиска As String
    Application.ScreenUpdating = False
 
    ТекстДляПоиска = "da"
 
    For Each cell In Range("g1:g30").Cells
        If cell = ТекстДляПоиска Then
            If delra Is Nothing Then Set delra = cell Else Set delra = Union(delra, cell)
        End If
    Next
 
    If Not delra Is Nothing Then delra.EntireRow.Hidden = False
    Application.ScreenUpdating = True
End Sub

Со скрытием строчек все отлично...но что делать, если в дальнейшем мне нужно некоторые скрытые строки вновь раскрыть (т.к. признак для скрытия для них может в будущем быть нерелевантен)?
Сделал такую билеберду, но скрытые строки он не раскрывает :( Видимо не видит скрытые строки. Помогите советом пожалуйста!

Sub Макрос()
Dim ra As Range, delra As Range, ТекстДляПоиска As String
Application.ScreenUpdating = False

ТекстДляПоиска = "da"

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("Наименование *", "Количество", _
                                  "текст?", "цен*сти", "*78*")
 
    ' перебираем все строки в используемом диапазоне листа
    For Each ra In ActiveSheet.UsedRange.Rows
        ' перебираем все фразы в массиве
If ra.row >= 17 then
        For Each word In УдалятьСтрокиСТекстом
            ' если в очередной строке листа найден искомый текст
            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 word
end if
    Next
 
    ' если подходящие строки найдены, то: (оставьте одну из 2 следующих строк)
    If Not delra Is Nothing Then delra.EntireRow.Hidden = True    ' скрываем их
    If Not delra Is Nothing Then delra.EntireRow.Delete    ' удаляем их
End Sub

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. Найти - пишете нужный Вам текст, поле "Заменить на" оставляете пустым.

а можете подсказать, как можно найти и удалить текст с определенной строки.

Все работает! Спасибо!

Спасибо! В понедельник на работе попробую.

Стас, у вас в коде ошибка в логике.
Попробуйте так:

DelStrTex = Array("*уровень 1*", "*рывани*", "*сечение*")
Dim DelRow As Boolean ' объявляем переменную
For Each ra In Sheets("O1").UsedRange.Rows
    DelRow = True ' по умолчанию - строка под удаление
    For Each word In DelStrTex ' перебираем все слова
        ' если очередное слово нашлось - помечаем строку как неудаляемую
        If Not ra.Find(word, , xlValues, xlPart) Is Nothing Then DelRow = False
    Next word
    ' если ничего из списка не нашлось - удаляем строку
    If DelRow Then If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
Next ra

Всем привет!
За код спасибо. Столкнулся с такой проблемой:

Пишу такой код и программа работает превосходно, удаляются строки, которые не содержат "*уровень 1*":
DelStrTex = "*уровень 1*"
For Each ra In Sheets("O1").UsedRange.Rows
For Each word In DelStrTex
If 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 word
Next

Но, когда хочу задать массив, чтобы программа удаляла строки, которые не содержат "*уровень 1*", "*рывани*", "*сечение*", программа удаляет все!

DelStrTex = Array("*уровень 1*", "*рывани*", "*сечение*")
For Each ra In Sheets("O1").UsedRange.Rows
For Each word In DelStrTex
If 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 word
Next

Почему так? Что я делаю не так?

Спасибо большое за макрос:) очень помог!

Спасибо работает.

Добавьте после строки
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
следующие строки
If ra.Row > 1 Then Set delra = Union(delra, ra.Offset(-1))
Set delra = Union(delra, ra.Offset(1))

Добрый день! Не подскажете, как удалять вместе с найденной строкой, предыдущую и следующую?

Артемий, для всех моих макросов нет никакой разницы, используется Excel 2007 или 2010
Ищите проблему в другом

Добрый день.
Подскажите пож-та, Ваш макрос у меня работает на ПК с Office 2007, но почему-то на ПК с Office 2010 не работает (не выполняется, и не выдает никаких ошибок)

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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