Как удалить строки по условию?
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Использовать встроенное средство 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 |
Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Если в данном коде в строке
вместо
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:
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 |
Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения(
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист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 ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Здравствуйте
Нужно из листа удалить строки, во всех столбцах которых есть 0. Т.е. если хотя бы в одном из столбцов строки попадается число отличное от 0, то строку не трогаем. Интуитивно понимаю, что тут подойдёт третий код из статьи, но как его откорректировать не очень понятно.
Здравствуйте.
в 3-м способе УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Хочу добиться обратного функционала - удалить строки в которых значения не совпадают с критериями
Если как в других способах менять условие If CStr(arr(li, 1)) = sSubStr Then на If CStr(arr(li, 1)) СИМВОЛ НЕ РАВНО (не распознается форумом) sSubStr Then
то удаляется весь столбец целиком...что я делаю не так?
С виду все верно. Скорее всего просто реально нет полностью совпадающих по значениям ячеек(регистр различается, лишние пробелы и т.п.)
Странно, у меня нет никаких отличий, но макрос тоже удаляет все.
Денис, могу лишь добавить - не видя данных и как что было изменено в коде ответ дать невозможно. Попробовал только что код - удаляет все как положено, ничего лишнего.
У меня тоже все удаляет((
Можно полный код который бы оставлял строки которые на Лист2 (Третий пример)?
Спасибо.
++++ проблема актуальна.
Как удалить всё, кроме тех значиений что есть на листе два (учет по частичному совпадению)
Голову уже сломал
Не удаляй найденные строки, а переноси их на новый лист.
Дмитрий, здравствуйте!
Подскажите пожалуйста, а в примере "УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)"
Макрос в умной таблице не работает?
Как Вам сказать, Алексей. Работает, но удалять строки не сможет, будет ошибка. Это особенность работы умных таблиц - из них нельзя удалять несмежные строки обычным удалением(Rows.Delete). Для этого надо существенно дополнять код, чтобы определять все ячейки, входящие именно в таблицу. Либо изначально ориентировать код на конкретную или выделенную умную таблицу.
Дмитрий, спасибо за оперативный ответ!
Выходит самое простое в этом случае уйти в диапазон, очистится, и снова вернуться в умную таблицу?
Рома, могу лишь повторить свой ответ Денису: код работает и удаляет только данные из списка. Не видя Ваших данных я не могу сказать почему у Вас удаляет все. Но проблема явно не в самом коде.
Один из самых полезных макросов, которые мне пригодились. Не хочу залазить в Access и такие макросы очень кстати.
Дмитрий, подскажите, пожалуйста, а как сделать, чтоб в третьем варианте удалялись строки, не содержащие значений из списка не по полному совпадению, а по частичному? Т.е. нужно изменить строку
If InStr(1, arr(li, 1), sSubStr, 1) > 0
Дарья, заменить знак Больше(>) на знак равно(=) не пробовали? :)
Хочу добавить несколько очевидных мыслей:
Макрос будет не удалять, а окрашивать искомые данные, если заменить строку:
If Not rr Is Nothing Then rr.EntireRow.Delete
на
If Not rr Is Nothing Then rr.EntireRow.Interior.Color = 65535
Макрос "удаление строк на основании списка значений(множественные критерии)",
требование к данным указанным на "Лист2" в столбце А(начиная с первой строки):
Минимальное количество значений не менее ДВУХ.
В массиве данных не должно быть ПУСТЫХ ячеек.
Большое СПАСИБО автору за труды!!!
Добрый день Дмитрий.
Присоединяюсь к вопросу заданному ранее:
Что изменить в третьем варианте макроса "удаление строк на основании списка значений(множественные критерии)" если необходимо удалить ячейки в которых нет совпадений? При условии частичного совпадения , строка 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, в коде придется менять больше, чем одну строку. Надо добавлять некий флаг. При каждой итерации цикла обнулять его. Но если хоть одно слово из списка есть в строке - менять его значение. По окончании цикла проверять значение флага. Возможно, позже дополню статью таким вариантом. Но пока нет времени "лепить" готовый код.
Спасибо за внимание к теме.
Дмитрий, а подскажите пожалуйста, можно ли дополить код так, чтобы он операцию по удалению строк мог производить на нескольких листах, например на всех трех листах одинаковые таблицы и чтобы 2 или 3 код, удалял строки по условию со всех трех листов? Заранее спасибо!
Здравствуйте.
Мне нужно удалять строки с пустыми ячейками.
В столбце имеются объединённые ячейки, и в результате они тоже удаляются.
Как сделать так, что бы такого не было? Что бы по условию если ячейка объединена, то её не нужно удалять, а просто пропустить.
Александр, могу только предположить, что надо в конкретном столбце проверять ячейку черезIf Cells(lr,1).MergeCells Then и в зависимости от этого либо удалять, либо нет.