Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (7)
Графики и диаграммы (5)
Работа с VB проектом (11)
Power BI и Power Query (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (61)
Разное (37)

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

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

Способ первый:
Использовать встроенное средство 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, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.

Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt+F8, после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке

вместо = lMet указать <> lMet, то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

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


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

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


Для всех приведенных кодов можно строки не удалять, а скрывать. Для этого надо строку:
If Not rr Is Nothing Then rr.EntireRow.Delete
заменить на такую:
If Not rr Is Nothing Then rr.EntireRow.Hidden = True

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


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

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

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

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

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

    0
  2. Артур:

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

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

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

      0
      • Денис:

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

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

          0
          • Рома:

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

            0
    • Антон:

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

      0
  3. Алексей:

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

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

      0
      • Алексей:

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

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

    0
  5. Сергей:

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

    0
  6. Дарья:

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

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

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

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

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

    0
  8. Добрый день Дмитрий.
    Присоединяюсь к вопросу заданному ранее:
    Что изменить в третьем варианте макроса "удаление строк на основании списка значений(множественные критерии)" если необходимо удалить ячейки в которых нет совпадений? При условии частичного совпадения , строка 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 заменить знак Больше(>) на знак равно(=), приводит к удалению всех строк с значениями в первом столбце.

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

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

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


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

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Генерация пароля