Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как удалить строки по условию?

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

Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:

  • Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
  • устанавливаем фильтр:
    • для Excel 2003: Данные-Фильтр-Автофильтр
    • для Excel 2007-2010: вкладка Данные(Data) -Фильтр(Filter)(или вкладка Главная(Home) -Сортировка и фильтр(Sort&Filter) -Фильтр(Filter))

Теперь выбираем условие для фильтра:

  • в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
  • Для 2007-2010 Excel нужно выбрать Текстовые фильтры(Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр(Custom Filter) и ввести значения для отбора в форме

После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.


 
Способ второй:
применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.

Sub Del_SubStr()
    Dim sSubStr As String 'искомое слово или фраза(может быть указанием на ячейку)
    Dim lCol As Long      'номер столбца с просматриваемыми значениями
    Dim lLastRow As Long, li As Long
    Dim lMet As Long
    Dim arr
 
    sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "www.excel-vba.ru", "")
    If sSubStr = "" Then lMet = 0 Else lMet = 1
    lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1))
    If lCol = 0 Then Exit Sub
 
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    arr = Cells(1, lCol).Resize(lLastRow).Value
    Application.ScreenUpdating = 0
    Dim rr As Range
    For li = 1 To lLastRow 'цикл с первой строки до конца
        If -(InStr(arr(li, 1), sSubStr) > 0) = lMet Then
            If rr Is Nothing Then
                Set rr = Cells(li, 1)
            Else
                Set rr = Union(rr, Cells(li, 1))
            End If
        End If
    Next li
    If Not rr Is Nothing Then rr.EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11 или вкладка Разработчик(Developer) -Visual Basic) -Insert -Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем Del_SubStr -Выполнить(Run).
Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Если в данном коде в строке If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet, то удаляться будут наоборот строки, не содержащие указанное для значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

Sub Del_SubStr()
    Dim sSubStr As String 'искомое слово или фраза(может быть указанием на ячейку)
    Dim lCol As Long 'номер столбца с просматриваемыми значениями
    Dim lLastRow As Long, li As Long
    Dim arr
 
    sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "www.excel-vba.ru", "")
    lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1))
    If lCol = 0 Then Exit Sub
 
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    arr = Cells(1, lCol).Resize(lLastRow).Value
 
    Application.ScreenUpdating = 0
    Dim rr As Range
    For li = 1 To lLastRow 'цикл с первой строки до конца
        If CStr(arr(li, 1)) = sSubStr Then
            If rr Is Nothing Then
                Set rr = Cells(li, 1)
            Else
                Set rr = Union(rr, Cells(li, 1))
            End If
        End If
    Next li
    If Not rr Is Nothing Then rr.EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения(Cells(li, lCol) = sSubStr) с равно на неравенство(Cells(li, lCol) <> sSubStr) и тогда удаляться будут строки, значения ячеек которых не равно указанному.


УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.

Sub Del_Array_SubStr()
    Dim sSubStr As String    'искомое слово или фраза
    Dim lCol As Long    'номер столбца с просматриваемыми значениями
    Dim lLastRow As Long, li As Long
    Dim avArr, lr As Long
    Dim arr
 
    lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1))
    If lCol = 0 Then Exit Sub
    Application.ScreenUpdating = 0
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    'заносим в массив значения листа, в котором необходимо удалить строки
    arr = Cells(1, lCol).Resize(lLastRow).Value
    'Получаем с Лист2 значения, которые надо удалить в активном листе
    With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление
        avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    'удаляем
    Dim rr As Range
    For lr = 1 To UBound(avArr, 1)
        sSubStr = avArr(lr, 1)
        For li = 1 To lLastRow 'цикл с первой строки до конца
            If CStr(arr(li, 1)) = sSubStr Then
                If rr Is Nothing Then
                    Set rr = Cells(li, 1)
                Else
                    Set rr = Union(rr, Cells(li, 1))
                End If
            End If
            DoEvents
        Next li
        DoEvents
    Next lr
    If Not rr Is Nothing Then rr.EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:

If CStr(arr(li, 1)) = sSubStr Then

заменить на такую:

If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.

'процедура оставляет в листе только те значения, которые перечислены в списке
Sub LeaveOnlyFoundInArray()
    Dim sSubStr As String   'искомое слово или фраза
    Dim lCol As Long        'номер столбца с просматриваемыми значениями
    Dim lLastRow As Long, li As Long
    Dim avArr, lr As Long
    Dim arr
    Dim IsFind As Boolean
 
    lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1))
    If lCol = 0 Then Exit Sub
    Application.ScreenUpdating = 0
    lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    'заносим в массив значения листа, в котором необходимо удалить строки
    arr = Cells(1, lCol).Resize(lLastRow).Value
    'Получаем с Лист2 значения, которые надо удалить в активном листе
    With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление
        avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    'удаляем
    Dim rr As Range
    For li = 1 To lLastRow 'цикл с первой строки таблицы до конца
        IsFind = False
        For lr = 1 To UBound(avArr, 1) 'цикл по списку значений на удаление
            sSubStr = avArr(lr, 1)
            If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then
                IsFind = True
            End If
            DoEvents
        Next lr
        'если значение таблицы не найдено в списке - удаляем строку
        If Not IsFind Then
            If rr Is Nothing Then
                Set rr = Cells(li, 1)
            Else
                Set rr = Union(rr, Cells(li, 1))
            End If
        End If
        DoEvents
    Next li
    If Not rr Is Nothing Then rr.EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Чтобы код выше сравнивал значения таблицы со значениями списка по точному совпадению слов, а не по частичному, то надо строку:

If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

заменить на такую:

If CStr(arr(li, 1)) = sSubStr Then

Для всех приведенных кодов можно строки не удалять, а скрывать. Для этого надо строку:

If Not rr Is Nothing Then rr.EntireRow.Delete

заменить на такую:

If Not rr Is Nothing Then rr.EntireRow.Hidden = True

По умолчанию все коды начинают просмотр строк с первой по последнюю заполненную на листе. И если необходимо удалять строки не с первой или не по последнюю, то надо внести корректировки в эту строку:

For li = 1 To lLastRow 'цикл с первой строки до конца

1 - это первая строка; lLastRow - определяется автоматически кодом и равна номеру последней заполненной строки на листе. Если надо начать удалять строки только с 7-ой строки(например, в первых 6-ти шапка), то код будет выглядеть так:

For li = 7 To lLastRow 'цикл с седьмой строки до конца

А если надо удалять только с 3-ей по 300-ю, то код будет выглядеть так:

For li = 3 To 300 'цикл с третьей строки до трехсотой

Так же см.:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
Как создать кнопку для вызова макроса на листе
Удаление всех пустых строк в таблице
Удаление пустых столбцов на листе
Установить Быстрый фильтр
Фильтр


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 150 комментариев
  1. Сергей:

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

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

  2. Артур:

    Здравствуйте.
    в 3-м способе УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
    Хочу добиться обратного функционала - удалить строки в которых значения не совпадают с критериями

    Если как в других способах менять условие If CStr(arr(li, 1)) = sSubStr Then на If CStr(arr(li, 1)) СИМВОЛ НЕ РАВНО (не распознается форумом) sSubStr Then
    то удаляется весь столбец целиком...что я делаю не так?

    • С виду все верно. Скорее всего просто реально нет полностью совпадающих по значениям ячеек(регистр различается, лишние пробелы и т.п.)

      • Денис:

        Странно, у меня нет никаких отличий, но макрос тоже удаляет все.

        • Денис, могу лишь добавить - не видя данных и как что было изменено в коде ответ дать невозможно. Попробовал только что код - удаляет все как положено, ничего лишнего.

          • Рома:

            У меня тоже все удаляет((
            Можно полный код который бы оставлял строки которые на Лист2 (Третий пример)?
            Спасибо.

    • Антон:

      ++++ проблема актуальна.
      Как удалить всё, кроме тех значиений что есть на листе два (учет по частичному совпадению)
      Голову уже сломал

  3. Алексей:

    Дмитрий, здравствуйте!
    Подскажите пожалуйста, а в примере "УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)"
    Макрос в умной таблице не работает?

    • Как Вам сказать, Алексей. Работает, но удалять строки не сможет, будет ошибка. Это особенность работы умных таблиц - из них нельзя удалять несмежные строки обычным удалением(Rows.Delete). Для этого надо существенно дополнять код, чтобы определять все ячейки, входящие именно в таблицу. Либо изначально ориентировать код на конкретную или выделенную умную таблицу.

      • Алексей:

        Дмитрий, спасибо за оперативный ответ!
        Выходит самое простое в этом случае уйти в диапазон, очистится, и снова вернуться в умную таблицу?

  4. Рома, могу лишь повторить свой ответ Денису: код работает и удаляет только данные из списка. Не видя Ваших данных я не могу сказать почему у Вас удаляет все. Но проблема явно не в самом коде.

  5. Сергей:

    Один из самых полезных макросов, которые мне пригодились. Не хочу залазить в Access и такие макросы очень кстати.

  6. Дарья:

    Дмитрий, подскажите, пожалуйста, а как сделать, чтоб в третьем варианте удалялись строки, не содержащие значений из списка не по полному совпадению, а по частичному? Т.е. нужно изменить строку
    If InStr(1, arr(li, 1), sSubStr, 1) > 0

  7. praga:

    Хочу добавить несколько очевидных мыслей:
    Макрос будет не удалять, а окрашивать искомые данные, если заменить строку:
    If Not rr Is Nothing Then rr.EntireRow.Delete
    на
    If Not rr Is Nothing Then rr.EntireRow.Interior.Color = 65535

    Макрос "удаление строк на основании списка значений(множественные критерии)",
    требование к данным указанным на "Лист2" в столбце А(начиная с первой строки):

    Минимальное количество значений не менее ДВУХ.
    В массиве данных не должно быть ПУСТЫХ ячеек.

    Большое СПАСИБО автору за труды!!!

  8. praga:

    Добрый день Дмитрий.
    Присоединяюсь к вопросу заданному ранее:
    Что изменить в третьем варианте макроса "удаление строк на основании списка значений(множественные критерии)" если необходимо удалить ячейки в которых нет совпадений? При условии частичного совпадения , строка If CStr(arr(li, 1)) = sSubStr Then заменена на: If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then.

    Проверял варианты предложенные ранее:
    If CStr(arr(li, 1)) = sSubStr Then, равенство (=) заменить на неравенство(), приводит к удалению всех строк с значениями в первом столбце.
    If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then заменить знак Больше(>) на знак равно(=), приводит к удалению всех строк с значениями в первом столбце.

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

  9. Андрей:

    Дмитрий, а подскажите пожалуйста, можно ли дополить код так, чтобы он операцию по удалению строк мог производить на нескольких листах, например на всех трех листах одинаковые таблицы и чтобы 2 или 3 код, удалял строки по условию со всех трех листов? Заранее спасибо!

  10. Александр:

    Здравствуйте.
    Мне нужно удалять строки с пустыми ячейками.
    В столбце имеются объединённые ячейки, и в результате они тоже удаляются.
    Как сделать так, что бы такого не было? Что бы по условию если ячейка объединена, то её не нужно удалять, а просто пропустить.

    • Александр, могу только предположить, что надо в конкретном столбце проверять ячейку через If Cells(lr,1).MergeCells Then и в зависимости от этого либо удалять, либо нет.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти