Find values from and array

Liam79

New Member
Joined
Jan 30, 2016
Messages
21
Hi guys, I've thrown in the towel looking online for the answer. Im quite basic at this so would really appreciate some help on the below.

I have this code that I found and it searches through data and deletes it when it finds a vlaue that I set. Without copying the code over and over again - is it possible to set the below small amount of code to search for about 4 items or so in stead of just the one.

At the moment it searches for the N1 and if I put in for example N1:N2 I still cant get it to search through for both items i need to finds.




Sub CLIENTOUT()
Dim FoundCell As Range
Application.ScreenUpdating = False
Set FoundCell = Range("A3:A200").Find(what:=Range("N1"))


Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("A4:A200").FindNext
Loop
End Sub


thanks again for your time
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
Code:
Sub CLIENTOUT()
Dim cl As Range
Dim FoundCell As Range

    Application.ScreenUpdating = False
    
    For Each cl in Range("N1:N2") ' change range to suit
        
        Set FoundCell = Range("A3:A200").Find(what:=cl)

        Do Until FoundCell Is Nothing
            FoundCell.EntireRow.Delete
            Set FoundCell = Range("A4:A200").FindNext
         Loop

    Next cl

End Sub
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
possible way to do it without a loop

Code:
Sub test()
Dim addr As String, myarray As String
addr = "A3:A200"
myarray = "N1:N2"
Range(addr).Value = Evaluate("IF(COUNTIF(" & myarray & "," & addr & ")=0," & addr & ")")
Range(addr).SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
End Sub
 

Forum statistics

Threads
1,136,776
Messages
5,677,667
Members
419,711
Latest member
dacrmcvega0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top