Как удалить строки по условию?
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Использовать встроенное средство 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 ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Добрый день. Скажите, пожалуйста, как заменить
sSubStr = InputBox("", "", "")
чтобы для удаления/выборки автоматически брать значение, которое в ячейке А1 или О4? Спасибо.
подскажи как разобрался
Спасибо, разобралась. Вопрос снимаю
Спасибо за код (особенно последний со столбцом удаляемых значений).
Подскажите как можно его изменить, чтобы результат (таблица с удаленными строками) помещался на Лист3 (Лист1 - исходная таблица, Лист2 - столбец значений на удаление)
Добрый день.
Подскажите пожалуйста как переделать третий код что бы удалял по частичному совпадению? Код приведенный на 5 стр. комментариев не работает.
Олег, попробуйте заменить
на
Мне подобный функционал был бы полезен, но очень низкая скорость работы макроса не позволяет работать с большими данными (таблицы более 200 000 строк). Попробовал на реальных данных третий макрос - вот уже больше часа жду, и всё никак не закончит работать и прервать не получается, ни Esc, ни Break не помогают. Придётся удалять лишние строки вручную, по старинке.
PS Надо было начинать читать с первого комментария :(
В любом случае спасибо за Ваш сайт, статьи и форум.
Виктор, попробуйте еще раз последний код. Я его только что чуть оптимизировал. Так же добавил возможность остановить выполнение кода принудительно нажатием клавиш Ctrl+Break
Дмитрий, подскажите, пожалуйста.
Удаляю повторяющиеся значения по третьему макросу, но почему-то удаляется только повторения последнего значение листа2, т.е. то, которое в самой последней строке, все значения, кроме последнего, игнорируются.
Спасибо.
Максим, либо удалите в уже имеющемся в файле коде строку:
Set rr = Nothing
либо скопируйте код из статьи заново(там я исправил).
Спасибо, Дмитрий, всё работает.
Спасибо большое, есть вопросик, а как изменить 3-й код "УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ), чтоб удалялись те строки значение которых не равно указанным в списке значений?
Светлана, для этого достаточно было прочесть статью полностью. Для второго кода я именно такую ситуацию и расписал. Достаточно = заменить на <>.
спасибо огромное!!! наверное не в том месте текста меняла, уже получилось)
Имею макрос на сбор данных с нескольких листов.
вписываю ниже макрос на удаление строк.
Sub Del_SubStr()
**********
sSubStr = ""
If sSubStr = "" Then lMet = 0 Else lMet = 1
lCol = "5"
If lCol = 0 Then Exit Sub
*********
End Sub
я как бы в макросах вообще не смыслю
но нужно удалять пустые строки, после того, как произошёл сбор данных из разных документов.
Владимир, проще прямо в тот же модуль, где код по сбору добавить один из приведенных выше кодов полностью и потом просто в конце кода по сбору(перед End Sub) вызывать код удаления строк:
Call Del_SubStr
при этом в самом коде жестко указать столбец для проверки значений. Тупо вместо строк:
записать так:
sSubStr = "что ищем для удаления" lCol = 5