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

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

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

Способ первый:
Использовать встроенное средство 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

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


Статья помогла? Не держи в себе, поделись ссылкой с друзьями!

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

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

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

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

    Рейтинг: 0
  2. Артур:

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

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

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

      Рейтинг: 0
      • Денис:

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

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

          Рейтинг: 0
  3. Алексей:

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

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

      Рейтинг: 0
      • Алексей:

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

        Рейтинг: 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
Перейти на страницу