У Анкура есть рабочий лист с тысячами ячеек, содержащих различные термины. Ему нужно удалить большое количество тех терминов и ячеек, в которых они встречаются. Например, ему может потребоваться удалить все ячейки, содержащие такие термины, как «google», «youtube», «linkedin» и многие другие термины. Анкур знает, что может выполнить поиск и заменить каждый из этих терминов, но это довольно утомительно. Он задается вопросом, есть ли способ определить все термины, которые нужно удалить, а затем заставить Excel удалить их из этих тысяч ячеек.

Если вам нужно делать такие вещи на регулярной основе, лучшим решением будет использование макроса. Хитрость заключается в том, как вы указываете в макросе именно то, что хотите заменить. Один из способов — поместить информацию в массив, как в следующем примере:

Sub RemoveTerms1()

Dim vTerm As Variant     Dim vArray As Variant

vArray = Array("google", "youtube", "linkedin")

For Each vTerm in vArray         Selection.Replace What:=vTerm, _           Replacement:="", LookAt:=xlPart     Next vTerm End Sub

Искомые термины помещаются в массив vArray, а затем проходит каждый член массива (каждый термин). Метод Replace используется с объектом Selection для фактических замен. Макрос не удаляет ячейки; он просто удаляет любой текст, соответствующий термину. При замене поиск нечувствителен к регистру, так что «google» соответствует «Google».

Поскольку используется объект Selection, важно убедиться, что вы выбрали список, который хотите обработать, перед фактическим запуском макроса.

В противном случае ничего не заменяется.

При желании вы можете создать макрос, который извлекает термины из диапазона ячеек в книге.

Sub RemoveTerms2()

Dim c As Range     Dim rngSource As Range     Dim vTerm As Variant     Dim arrTerms As Variant     Dim i As Integer

i = -1     arrTerms = Array()

For Each c In Range("D1:D9").Cells         If Trim(c.Value) > "" Then             i = i + 1             ReDim Preserve arrTerms(i)

arrTerms(i) = Trim(c.Value)

End If     Next c

On Error Resume Next     Set rngSource = Application.InputBox( _                     Prompt:="Please select Range", _                     Title:="Removing Cells Containing Terms", _                     Default:=ActiveSheet.UsedRange.Address, Type:=8)

On Error GoTo 0     If rngSource Is Nothing Then         MsgBox ("You didn't specify a range to process")

Else         For Each vTerm in arrTerms             rngSource.Replace What:=vTerm, _               Replacement:="", LookAt:=xlWhole         Next vTerm     End If End Sub

Этот макрос извлекает условия поиска из диапазона D1: D9, а затем предлагает вам выбрать диапазон ячеек, которые вы хотите обработать. Он использует тот же метод Replace, который использовался в предыдущем макросе, за исключением того, что для параметра LookAt указывается значение xlWhole. Это означает, что поисковый запрос должен соответствовать всей ячейке, чтобы его можно было удалить. Тем не менее, термины по-прежнему считаются нечувствительными к регистру.

Обратите внимание, что приведенные до сих пор примеры фактически не удаляют никакие ячейки; они просто удаляют содержимое ячеек. Во многих случаях это именно то, что вам нужно, потому что вы не хотите нарушать макет фактического рабочего листа. Если вы действительно хотите удалить ячейки, вы бы не использовали метод Replace. Вместо этого вы можете включить расширенное сравнение текста и использовать оператор Like, чтобы увидеть, есть ли совпадение.

Option Compare Text

Sub RemoveTerms3()

Dim c As Range     Dim rngSource As Range     Dim vTerm As Variant     Dim arrTerms As Variant     Dim i As Integer     Dim sLook As String

i = -1     arrTerms = Array()

For Each c In Range("D1:D9").Cells         If Trim(c.Value) > "" Then             i = i + 1             ReDim Preserve arrTerms(i)

arrTerms(i) = Trim(c.Value)

End If     Next c

On Error Resume Next     Set rngSource = Application.InputBox( _                     Prompt:="Please select Range", _                     Title:="Removing Cells Containing Terms", _                     Default:=ActiveSheet.UsedRange.Address, Type:=8)

On Error GoTo 0     If rngSource Is Nothing Then         MsgBox ("You didn't specify a range to process")

Else         For Each vTerm in arrTerms             sLook = "" & vTerm & ""

For Each c In rngSource                 If c.Value Like sLook Then c.Delete             Next         Next vTerm     End If End Sub

Обратите внимание, что поисковые запросы по-прежнему берутся из диапазона D1: D9, и вас по-прежнему запрашивают диапазон, который вы хотите обработать. Однако оттуда процесс отличается: макрос исследует каждую ячейку и, если есть частичное совпадение, ячейка удаляется.

Чтобы этот вариант макроса работал должным образом, вам необходимо включить строку Option Compare Text вне самой процедуры.

Это инструктирует VBA включить ключевые слова (например, Like), позволяющие сравнивать текст.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13373) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.