删除不包含多个指定单词之一的单元格 [英] Delete cells which do not contain one of mutiple specified words

问题描述

我有一个Excel代码，可以删除所有我没有输入的内容.

I have an Excel code that removes everything that I do not enter.

如果用户输入狗，猫，房子，然后结束所有包含单词狗，猫和房子"的单元格，则应保留.其他所有单元格都应删除.

If a user enters dog, cat, house and then END all cells containing the word dog, cat and house should remain. All other cells should be removed.

现在，如果用户先写例如Dog然后再写END，它就可以工作.然后它将删除所有单元格，但显示狗"的位置.如果用户输入以下内容，则我需要它工作:狗，猫，房子，然后结束

Right now, it works if the user writes for example Dog and then END. Then it will remove all cells but where it says Dog. I need it to work if the user writes: Dog, Cat, House and then END

Sub test()

    Dim objectsToRemove As New Collection '<-- collection to store all the strings
    Dim currentObject As String

    Do While currentObject <> "END" '<-- while the input is different than "END", we keep on asking inputs
        currentObject = InputBox("which objects do you want to keep, ie some on the screen:")
        If currentObject <> "END" Then objectsToRemove.Add currentObject '<-- and add them to the collection
    Loop

    Dim myrange As Range

    Set myrange = ThisWorkbook.Worksheets("Tabell").UsedRange

    For Each cell In myrange.Cells
        For Each obj In objectsToRemove '<-- we apply the same logic of yours to each obj inside objectsToRemove
            If Not (cell.Value Like obj & "*") Then
                cell.Value = ""
            End If
        Next obj
    Next

End Sub

推荐答案

您需要将单元格清除代码放入循环之外.使用 boolean 检查是否应清除单元格:

You need to put the cell clearing code outside your loop. Use a boolean to check if the cell should be cleared:

Dim someBool as Boolean ' Should be named something meaningful to you
                        ' to understand the logic 6 months down the line

' Other code

For Each cell In myrange.Cells
    someBool = True
    For Each obj In objectsToRemove ' Should be named objectsToKeep
        If (cell.Value Like "*" & obj & "*") Then
            someBool = False
        End If
    Next obj
    If someBool Then cell.Value = ""
Next

这篇关于删除不包含多个指定单词之一的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！