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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top