Есть столбец со словосочетаниями (Поисковые запросы) Лист1, столбец A. Есть столбец со словами (Список минус слов) Лист2, столбец A. Нужно удалить все строки со словосочетаниями, содержащие хотя бы одно из слов списка, без учета регистра (в идеале но не обязательно). пример.xlsx(15.95 КБ) Искал везде где можно, но то что находил не получалось заставить работать. Самое близкое что нашел по данной задаче: [
Скрытый текст
COLOR=#626262]УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений. Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия"[/COLOR]
Sub Del_Array_SubStr() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "Запрос параметра", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = lLastRow To 1 Step -1 If CStr(Cells(li, lCol)) = sSubStr Then Rows(li).Delete Next li Next lr Application.ScreenUpdating = 1 End Sub
Помогите пожалуйста с задачей, уверен для знатоков это не составит особого труда, а я уже неделю почти не могу решения найти. Заранее благодарю!
Следующую абракадабру запихиваете в модуль первого листа и говорите экселю переварить всё енто.
Скрытый текст
Код
Option Explicit
Sub jjj()
Dim Rng As Range, i&, j&, arr, cnt&, ars As Range, rngDel As Range
Set Rng = Me.Range("A1").CurrentRegion
arr = ActiveWorkbook.Worksheets("Лист2").Range("A1").CurrentRegion.Value
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
Rng.AutoFilter Field:=1, Criteria1:= _
"=*" & arr(i, j) & "*", Operator:=xlAnd
cnt = 0
Set rngDel = Nothing
For Each ars In Rng.SpecialCells(xlCellTypeVisible).Areas
cnt = cnt + 1
If cnt = 2 Then
Set rngDel = ars
ElseIf cnt > 2 Then
Set rngDel = Application.Union(rngDel, ars)
End If
Next ars
If cnt > 1 Then rngDel.EntireRow.Delete
Rng.AutoFilter Field:=1
Next j
Next i
End Sub
Сим-салабим-ахалай-махалай-ляськи-масьськи! Всё готово. Магия... =)))
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
Большое спасибо что озадачились моей проблемой! Попытался засунуть данный код, но строптивый Exel говорит что тут не все так просто... магия в неумелых руках не работает... Я заменил jjj на Минусовка но в общем проблема была и с jjj
Kuzmich написал: Почему не удалились строки с Абхазия?
У меня всё что нужно удалено. Проверял после макроса формулой ключевые слова формулой: =СЧЁТЕСЛИ(Лист1!$A:$A;"*"&$A1&"*") По всем словам нули. Вручную запустил поиск указанного Вами слова - ничего не найдено. Mezomaster, читайте приёмы про макросы. (чойта и с чем енто едять)
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
JayBhagavan Три первые строки со словом абхазия (с маленькой буквой а) не удаляются. На листе 2 - Абхазия Хотя, я думаю, что ТС предполагал их удаление
Kuzmich, я затрудняюсь ответить почему этот макрос так срабатывает у Вас. Возможно где-то в коде надо включить опцию не различать регистр? (просветите меня пожалуйста) У меня этих строк после работы макроса не остаётся. Спасибо за Ваши замечания. _/\_
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
JayBhagavan Для меня осталось загадкой, почему срабатывает фильтр, если критерий = *Абхазия*, а строки выделяются со словом абхазия и второе, макрос именно строки с абхазия не идентифицирует как Areas Excel 2003
Kuzmich, кажется я понял. В файле-примере, я добавил строчку для заголовка таблицы, чтобы обрабатываемые данные не попадали в заголовок, а заголовок (первая строка) макросом всегда пропускается. (для этого и завёл счётчик) Моя оплошность в том, что не указал это уточнение ранее. Прошу прощения.
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
Друзья, добрый день, Ситуация схожа с тем что в шапке, за исключением: Если на ЛИСТ1 в любой из строк находится хоть одно слово из столбца А (в нем много строк) который находится на ЛИСТ2, то строка (находящаяся на ЛИСТ1) содержащая хотя бы одно слова из столбца А (ЛИСТ2) удаляется.
Для Excel из под MAC отдельная ветка есть. Там далеко не каждый макрос, написанный для Excel Windows, заработает. Но судя по ошибке - Вы совершили её сами. Код должен располагаться в модуле листа, а не в Module1. Либо замените Me на ActiveSheet(тогда работать будет с активным листом активной книги). Но я весь код не проверял, могут быть тогда какие-нибудь другие бяки вылезти.
После того как вставил и запустил на проверку Эксель завис. Висим минут 10 (в документе 26 000 строк. Такое время зависания нормально?) Полсе скрипт выполнился, но не совсем так как я хотел, а именно:
Поиск был только в столбце А (лист1) как изменить формулу чтоб искало слова на всем лист1 а не только в строке А1
Как я понял макрос берет слово только из ячейки А1 (Лист2) а не из всего столбца А (если да То как прописать столбец? А:А так?)
из меня программист тот еще, по этому заранее прошу прощения если они покажутся вам глупыми)
Karniel написал: чтоб искало слова на всем лист1 а не только в строке А1
Код так и делает. Вопрос в том, что код писался явно под другой пример данных. И сейчас он просто берет всю таблицу, которая начинается с А1 и не прерывается до конца. Т.е. таблица с началом в А1 без пропусков в виде пустых строк и столбцов.
Дмитрий Щербаков написал: Без Вашего примера данных никто Вам не исправит код
Прилагаю файл примера. Таблицу СИИЛЬНО урезал как в столбцах так и в графах (для экономии времязатрат на проверку макроса) на самом деле столбцов с текстом на лист1 больше чем один. Лист1 - поле для формата Лист2 Минусслова
А какой результат ожидаете? Вы понимаете, что в данном случае, будут удалены все СТРОКИ, в которых будут найдены "Минусслова"? И в общем - код работает. Я его поместил в модуль "Лист1", запустил - он все удалил как положено. Правда, добавил чутка оптимизации для скорости(самую малость):
Код
Sub jjj()
Dim Rng As Range, i&, j&, arr, cnt&, ars As Range, rngDel As Range
Set Rng = Me.Range("A1").CurrentRegion
arr = ActiveWorkbook.Worksheets("Ëèñò2").Range("A1").CurrentRegion.Value
Application.ScreenUpdating = 0
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
Rng.AutoFilter Field:=1, Criteria1:= _
"=*" & arr(i, j) & "*", Operator:=xlAnd
cnt = 0
Set rngDel = Nothing
For Each ars In Rng.SpecialCells(xlCellTypeVisible).Areas
cnt = cnt + 1
If cnt = 2 Then
Set rngDel = ars
ElseIf cnt > 2 Then
Set rngDel = Application.Union(rngDel, ars)
End If
Next ars
If cnt > 1 Then rngDel.EntireRow.Delete
Rng.AutoFilter Field:=1
Next j
Next i
Application.ScreenUpdating = 1
End Sub
Дмитрий Щербаков написал: А какой результат ожидаете? Вы понимаете, что в данном случае, будут удалены все СТРОКИ, в которых будут найдены "Минусслова"?
Все верно, цель макроса удалить всю строку целиком, если в ней найдено хотя бы 1 из минусслов. Использовать буду макрос для корректировки выгрузок из 1с, которая не умеет вписывать много минусслов в запрос.
Либо я что то делаю не так, либо лыжы не едут. то что надо заменить абракадабру ("Ëèñò2") на "Лист2" я догодался, Но скрипт не удалил ни одной строки. Что я делаю не так? уже перешел на ПК с виндой. все равно не удаляет.
Может быть изза того что В оригинальной таблице Перед тектовым столбцом стоит еще несколько столбцов с данными.
Karniel написал: изза того что В оригинальной таблице
Цитата
Дмитрий Щербаков написал: Без Вашего примера данных никто Вам не исправит ко
В общем при таком подходе интерес к теме потерян. Я приложил файл с кодом. Там все работает? Удаляет? Если да - угадайте с трех раз, почему и зачем просят файлы примеры именно в той структуре, которая является реальной. Если не хотите прикладывать файл с реальной структурой - то желания сидеть и угадывать какая она у Вас и пытаться под неё коды изменить желания ни у кого не будет. Предположу, что Вам куда выгоднее будет взять мой код(который в первом сообщении привел автор темы): Как удалить строки по условию? Он в самом конце статьи и там можно указать номер столбца, в котором искать значения для удаления.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Дмитрий Щербаков написал: Если не хотите прикладывать файл с реальной структурой
Прошу прощения если я Вас обидел, я этого не хотел. Я не прикладывал файл, потому что он весит 112 Мб. Вот реальная структура файла, но удалены строки. после 200+ (чтоб не скачивать и не прорабатывать файл.) Макрос вписываю конкретно в него
Option Explicit
Public Sub Медленно()
Dim Minus As Range, Dest As Range
Application.ScreenUpdating = 0
On Error Resume Next
For Each Minus In Worksheets(2).UsedRange
For Each Dest In Worksheets(1).Columns("H").SpecialCells(xlCellTypeConstants)
If InStr(Dest.Value, Minus.Value) > 0 Then
DoEvents
Application.StatusBar = "Строка " & Dest.Row & " из " & Worksheets(1).UsedRange.Rows.Count
Dest.EntireRow.Delete
End If
Next
Next
Application.ScreenUpdating = 1
End Sub
Дмитрий, я скачал ваш пример файла, вставил в 1 лист кучу слов, во 2 лист кучу минус слов. На первый взгляд все прошло успешно, но почему-то с 1 листа удаляется "химчистка мебели", хотя в 2 таких слов нет. подскажите в чем причина? Файл прикладываю
Странно. Скачал ваш файлик снова и с него ничего не удаляется, а с того который приложил, удаляется то, что не должно)