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

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

(пример - во вложении 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

Комментарии

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

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

Кавычки внутри строки надо продублировать:

ТекстДляПоиска = "ООО ""СБСВ-КЛЮЧАВТО-КРАСНОДАР"""   ' удаляем строки с таким текстом

Добрый день, проблема такая, изменяю строчку в такой вид, текст в строке, который нужно удалить ООО "СБСВ-КЛЮЧАВТО-КРАСНОДАР" - именно с кавычками.
ТекстДляПоиска = "ООО "СБСВ-КЛЮЧАВТО-КРАСНОДАР"" ' удаляем строки с таким текстом
выдает ошибку.

Все работает! Большое спасибо!!!

Здравствуйте, Илья

замените строку

'If Not delra Is Nothing Then delra.EntireRow.Hidden = True	' скрываем их

на
If Not delra Is Nothing Then delra.EntireRow.Copy Worksheets.Add.Range("a1") ' копируем на новый лист

Здравствуйте!

Во-первых огромное спасибо за данный макрос!!! Ооочень долго искал его :)
Во-вторых есть вопрос: использую поиск по массиву ("по нескольким условиям"), дописал выделение цветом найденного (последняя строка перед End Sub):

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

' ищем и удаляем строки, содержащие заданный текст
' (можно указать сколько угодно значений, и использовать подстановочные знаки)
МассивДляПоиска = Array("*ТекстКоторыйИщем*","*ДругойТекстКоторыйИщем*")

' перебираем все строки в используемом диапазоне листа
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

' если диапазон не пуст, то: (оставьте одну из следующих строк)
'If Not delra Is Nothing Then delra.EntireRow.Hidden = True ' скрываем их
'If Not delra Is Nothing Then delra.EntireRow.Delete ' удаляем их
If Not delra Is Nothing Then delra.EntireRow.Interior.ColorIndex = 45 ' заливаем цветом (оранж)
End Sub
//простите не знаю как вставлять код в комментарий - буду признателен, если поясните

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

Заранее благодарен!

Да, спасибо. еще я забыла задать лист, с которым надо работать

А не работает из-за вашей чрезмерной любви к кавычкам вокруг цифр/
Вот так заработает (номер столбца 8 - без кавычек):

If Not delra Is Nothing Then Intersect(delra.EntireRow, Columns(8)).Value = 0

попробовала оба варианта, тоже не срабатывает.
принцип такой: проходимся по всему столбцу G,ищем там все значения, не равные нулю и в соседнем (то есть столбце H) проставляем ноль

0 и "0" - для макроса это разные вещи

попробуйте такие варианты:

If cell1 <> 0 Then

If cstr(cell1) <> "0" Then

Пытаюсь заменить значение в 8 колонке при соблюдении условия, макрос не срабатывает
при этом ошибок не выдает

Dim ra As Range, delra As Range, cell1 As Range
Set ra = Range([G1], Range("G" & Rows.Count).End(xlUp)) 'ведем поиск по столбцу G

Application.ScreenUpdating = False
For Each cell1 In ra.Cells
If cell1 <> "0" Then ' если значение в столбце G не равно 0
' добавляем такие ячейки в диапазон
If delra Is Nothing Then Set delra = cell1 Else Set delra = Union(delra, cell1)
End If
Next cell1
' строки найдены, тогда в 8 столбце присваиваем значение 0
If Not delra Is Nothing Then Intersect(delra.EntireRow, Columns("8")).Value = "0"
' ------------------------------------------

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

а как подправить макрос что бы он удалял все строки кроме одного заданного текста?
Макрос по нескольким условиям не подойдет, так как других текстов очень много.

Спасибо. Макрос без правки отлично работает, но в мое случае используется 6 столбец, и при изменении столбца макрос отказывается работать. Не понимаю как такое возможно. :)

        ' ищем слово "показать" в 3 столбце (номер столбца можете поменять)
       For Each cell In Intersect(sh.UsedRange, sh.Columns(6)).Cells

Исправил макрос в предыдущем комменте
Проверяйте

Необходимо также, чтобы проверка была по всей книге и страницам. Да и при запуске выдает ошибку Compile error: Invalid Next control variable reference

Здравствуйте, Евгений.
Так попробуйте:

Sub ОтображениеСтрок()
    Dim cell As Range, sh As Worksheet
    Application.ScreenUpdating = False        ' отключаем обновление экрана

    ' перебираем все листы в активной книге
    For Each sh In ActiveWorkbook.Worksheets
        ' ищем слово "показать" в 3 столбце (номер столбца можете поменять)
        For Each cell In Intersect(sh.UsedRange, sh.Columns(3)).Cells
            If cell.Value = "показать" Then cell.EntireRow.Hidden = False        ' отображаем строку
        Next cell
    Next sh        ' переходим к следующему листу

    Application.ScreenUpdating = True        ' включаем обновление экрана
End Sub

Здравствуйте. Как отобразить, ранее скрытые строки, если значение "скрыть" изменилось по формуле на "показать"??? И чтобы отображались только заданные строки, так как имеются и другие скрытые строки с другими данными.

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

    ' ищем и скрываем строки, содержащие заданный текст
  ' (можно указать сколько угодно значений, и использовать подстановочные знаки)
  СкрыватьСтрокиСТекстом = Array("скрыть")
 
    ' перебираем все листы в активной книге
  For Each sh In ActiveWorkbook.Worksheets
        ' очистка переменной при переходе к следующему листу
      Set delra = Nothing
 
        ' перебираем все строки в используемом диапазоне очередного листа
      For Each ra In sh.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 ra
 
        ' если подходящие строки найдены, то:
      If Not delra Is Nothing Then delra.EntireRow.Hidden = True    ' скрываем их

    Next sh    ' переходим к следующему листу
End Sub

Здравствуйте. Помогите пожалуйста. Мне надо скрыть два столбца с данными (например: А:В), а данные копировались в ячейки С:D и дополнительно вставлялись справа от C:D столбцы с формой заполнения. Записываю макрос, но у меня скрывается вся таблица. Помогите пожалуйста.

А поискать решение в комментариях к статье не пробовали?

Чтобы макрос обрабатывал ВСЕ листы в книге, используйте такой вариант кода:

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

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

    Next sh    ' переходим к следующему листу
End Sub

Где что поменять чтобы операцию производил не только на текущем листе, а на всех страницах книги?

Надо знать, что удалять, что оставлять (иметь где-то список правил)
Исходя из этого, уже и думать, как написать код (его «с нуля» придется писать)

Могу сделать под заказ. Или обратитесь на форумы по Excel, - там бесплатно помогут.

Не могли бы Вы "направить на путь истинный"?! Как,чем можно доработать код, чтобы он учитывал эти ограничения?

Здравствуйте, Сергей.
В этой версии макроса - никак не сделать (путем изменения искомых значений)
Надо код дорабатывать (переписывать)

Доброго времени суток!
Возник вопрос насчет макроса с удалением строк по нескольким условиям. Допустим, в условии для удаления стоит
"423*",
но! Мне нужно чтобы не все строки со значениями с 423 в начале удалялись. К примеру, это счета "42309*", которые нужно оставить.
Как можно в макросе это ограничить?

Спасибо автору! Отлично работает

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

Помогите пож. Имеется кнопка с формулой для вычисления функции.
Надо чтобы эта кнопка брала значение с выделенной ячейки и заносила ответ в определенную ячейку например в ячейку E7.
Спасибо большое)

я макросы писать не умею, но зато хорошо знакома с формулами экселя. Вам просто нужно использовать функцию ВПР,определив какие строки совпадают как-нибудь их обозначить или покрасить, остальное удалить через фильтр удалить

товарищи! а как сделать простой простой макрос, который будет оставлять только те строки, которые совпадают с данными из другой колонки? ибо нет мочи 8000 строк шаманить(((((((

О,круть!работает!спасибо огромное!
А можно ли записать строку If ra.cells(4)=0 and ra.cells(5)=0 and ra.cells(6)=0 and ra.cells(1)="" Then по другому?.к примеру, мне нужно указать не 3 столица,которые будут равны 0,а 15 столбцов должны быть равны 0.есть ли возможность записать это диапазонам, а не перечислять каждый столбец?

Здравствуйте, Майя.
Примерно так:

 ' перебираем все строки в используемом диапазоне листа
   For Each ra In ActiveSheet.UsedRange.Rows
        ' если выполняются одновременно следующие 4 условия
       If ra.cells(4)=0 and ra.cells(5)=0 and ra.cells(6)=0 and ra.cells(1)="" Then
            ' добавляем строку в диапазон для удаления
           If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
        End If
    Next

Подскажите, а как написать, чтобы строки удалялись, если только выполняется одновременно условие. Мне надо, чтобы строки удалялись, если, к примеру, в столбцах D,E,F стоит значение 0, а в столбце A пусто.

Здравствуйте, Александр.
В обоих случаях, для подсчёта строк delra.count не подойдёт.
Вариант с delra.areas.count может подойти для проверки одного условия (при поиске в одном столбце)

Для подсчёта количества удаляемый строк, лучше использовать такой код:

КоличествоСтрок = intersect(delra.entirerow, delra.entirerow, columns(1)).cells.count

Здравствуйте,
Подскажите, что бы в макросе УдалениеСтрокПоУсловию() подсчитать сколько строк удалено достаточно delra.count
а как посчитать сколько строк удалено в макросе УдалениеСтрокПоНесколькимУсловиям()

Добрый день. Помогитежалста с макросом.
В столбце связка из 4 ячеек. Когда в первой ячеике положительное число, надо, чтобы делитились следующие три строки. Если в первой 0, надо чтобы делитились она. Далее то же самое со второй ячейкой в связке.
Помогите пожалуйста по возможности.

Мне нужно удалить строки, где встречается ? Т.е. все строки с ?

Здравствуйте.
Подскажите, как в этом макросе
Sub УдалениеСтрок()
Dim ra As Range, delra As Range, cell As Range
Set ra = Worksheets("2").Range("F27:F43") ' диапазон в столбце F с 27 по 43 ячейку в листе 2

Application.ScreenUpdating = False ' отключаем обновление экрана

' перебираем ячейки в столбце F , и выбираем только пустые или равные 0

For Each cell In ra.Cells
If (cell = "0" Or cell = "") 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 delra.EntireRow.Hidden = True
End Sub
сделать нумерацию видимых ячеек( после выполнения скрытия) в столбце "В" по порядку?

Сделать можно что угодно.
Можно встроенными средствами обойтись, - включив автофильтр, снять галочку на нужном столбце со значения «пустые»
Если нужен именно макрос, - оформляйте заказ на сайте (или обратитесь на форумы по Excel, если хотите бесплатно)

а можно сделать так?:

Условие:
1) В столбцах содержаться ячейки с данными и пустые ячейки.
2) В каждом столбце расположение и количество ячеек с данными и ячеек пустых отличается и статично (если ячейка пуста, то она пуста всегда и если с данными то с данными всегда).

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

Спасибо.

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

Здравствуйте,подскажите пожалуйста как в экселе сделать автоматическое добавление строк по вводимому значению в ячейке?
Допустим ввожу число 2 добавляется 2 строки,10-10 строк и так далее

всем привет!
помогите пожалуйста с редактированием таблицы..
есть одна большая таблица с клиентами и данными о них в столбцах..первый столбец отвечает за регион, например, Поволжье..пусть тут будет 10 клиентов..2ой, 3ий и т.д.регионы с разным количеством клиентов..второй столбец название клиента
клиенты меняются, поэтому необходимо удалять уже существующих клиентов (по их названию)..и добавлять новых в определенный регион (т.е. это и будет условием)
как можно рещить эту проблему? неужели только с VBA?
буду очень благодарна за помощь

Здравствуйте.
Подскажите пожалуйста, как сделать следующее:
Нужно удалить все строки кроме тех, в которых содержится слово "слон"
Потратил 3 часа в интернете, перепробовал все - надежда только на вас
Заранее спасибо

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

Спасибо Вам огромное!!!

Art, надо немного изменить код.

Вместо

' если в очередной строке листа найден искомый текст
If Not ra.Find(word, , xlValues, xlPart) Is Nothing Then

напишите

' если в столбце 5 находится искомый текст (номер столбца сами нужный поставите)
If ra.entirerow.cells(5) = word Then

Здравствуйте. Пользуюсь макросом по скрытию строк по нескольким усовиям.
Есть столбец, в котором забита формула "если" и несколько вариантов ответа (Выполнено, перевыполнено, не выполнено). Хочу скрыть все строки и оставить только "не выполнено". Задаю в макросе два других варианта, но в итоге скрывает вообще все) Скорее всего, потому что в каждой ячейке присутствуют все варианты... Поэтому хочу спросить, возможно ли сделать так, чтобы макрос считывал только конечное значение ячейки, то есть результат?

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

1) замените строку
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
на
If delra Is Nothing Then Set delra = ra.RESIZE(6) Else Set delra = Union(delra, ra.RESIZE(6))

2) замените строку
If Not delra Is Nothing Then delra.EntireRow.Delete
на
If Not delra Is Nothing Then worksheets("ИмяЛиста").range(delra.address).EntireRow.Delete

Спасибо!
Подскажите, а как можно с помощью этого макроса:
1) выбрать для удаления строку, в которой найдено искомое слово + еще 5 строк ниже?
2) выбрать строки для удаления на одном листе книги, а удалить их (строки с теми же индексами) на другом листе?

СПАСИБО!

Mutabor, используйте цикл по всем листам книги.

Делается это примерно так:

Sub test()
    Application.ScreenUpdating = False
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets    ' перебираем все листы в книге
        sh.Activate    ' активируем очередной лист
        УдалениеСтрокПоУсловию    ' запускаем макрос
    Next sh
End Sub

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

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