Как в excel удалить строки с определенным словом
- Главная
- Excel
- Готовые решения
- Форумы
- Библиотека
- Видеосалон
- Справочники
- Разработчик
Информация о сайте
Инструменты и настройки
Excel Windows
и
Excel Macintosh
Вопросы и решения
Работа и общение
Работа форума и сайта
Функции листа Excel
= Мир MS Excel/Как удалить строку с определенным словом в строке — Мир MS Excel
Войти через uID
Войти через uID
Поиском не пробовали пользоваться?
"Удаление строк по условию"
Или воспользуйтесь "Фильтр — текстовые фильтры — не содержит" и так по всем столбцам, где встречается ваше слово.
Потом просто скопировать на новый лист результат фильтрации.
Поиском не пробовали пользоваться?
"Удаление строк по условию"
Или воспользуйтесь "Фильтр — текстовые фильтры — не содержит" и так по всем столбцам, где встречается ваше слово.
Потом просто скопировать на новый лист результат фильтрации. SkyPro
Сообщение Поиском не пробовали пользоваться?
"Удаление строк по условию"
Или воспользуйтесь "Фильтр — текстовые фильтры — не содержит" и так по всем столбцам, где встречается ваше слово.
Потом просто скопировать на новый лист результат фильтрации. Автор — SkyPro
Дата добавления — 25.07.2013 в 14:42
(ВЫ ПИСАЛИ SkyPro: Поиском не пробовали пользоваться?)
Не хочу показаться грубым но вы наверное не дочитали.
(ВЫ ПИСАЛИ SkyPro: [url=http://www.excelworld.ru/forum/2-5524-55878-16-1374748957]25.07.2013 в 14:42, в сообщении №2 Или воспользуйтесь "Фильтр — текстовые фильтры — не содержит" и так по всем столбцам, где встречается ваше слово.
Потом просто скопировать на новый лист результат фильтрации.
)
Ну эти слова я то найду с помощью фильтров но мне надо убрать строки, их очень много, фильтром и поиском это долго.
(ВЫ ПИСАЛИ SkyPro: Поиском не пробовали пользоваться?)
Не хочу показаться грубым но вы наверное не дочитали.
(ВЫ ПИСАЛИ SkyPro: [url=http://www.excelworld.ru/forum/2-5524-55878-16-1374748957]25.07.2013 в 14:42, в сообщении №2 Или воспользуйтесь "Фильтр — текстовые фильтры — не содержит" и так по всем столбцам, где встречается ваше слово.
Потом просто скопировать на новый лист результат фильтрации.
)
Ну эти слова я то найду с помощью фильтров но мне надо убрать строки, их очень много, фильтром и поиском это долго. Виталий
Сообщение (ВЫ ПИСАЛИ SkyPro: Поиском не пробовали пользоваться?)
Не хочу показаться грубым но вы наверное не дочитали.
Как удалить строки по условию?
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство 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 не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
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
‘процедура оставляет в листе только те значения, которые перечислены в списке 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 ‘цикл с третьей строки до трехсотой
Статья помогла? Поделись ссылкой с друзьями!