Find multiple criteria

[Mike2]

Hello,

I am looking to use the Find function to find multiple criteria.
Here is my code:

Dim mFind As Variant
Dim i As Integer

mFind = Array("LetterA", "LetterC", "LetterE", "LetterH", "LetterL", "LetterO", "LetterP", "LetterR")
For i = LBound(mFind) to UBound(mFind)
Rows(1).Find(What:=mFind(i), LookIn:xlValues, Lookat:=xlWhole, MatchCase:=False).EntireColumn.Delete
Next

After executing this macro, Columns "LetterA", "LetterC", "LetterE" got deleted successfully,
then it stops and pops out an Error Runtime 91 for Object-Variable or With Block Variable Not Set

Can someone please help to explain why i got this error message and how can i overcome this coding error?
I am open for an alternative coding suggestion to resolve this issue. Your help is greatly appreciated.

Thanks.

 

[Akuini]

Maybe this:

Dim mFind As Variant
Dim i As Integer
Dim c As Range
mFind = Array("LetterA", "LetterC", "LetterE", "LetterH", "LetterL", "LetterO", "LetterP", "LetterR")
For i = LBound(mFind) To UBound(mFind)

Set c = Rows(1).Find(What:=mFind(i), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then c.EntireColumn.Delete
Next

 

[Mike2]

Thank you Akuini for your suggestion.

But it's still does not work. I run into the same issue, only Columns "LetterA", "LetterC", "LetterE" got deleted successfully then the macro stopped.
When i re-run the macro again it gives me Error Run-time 91.

Is there another way to do this? It looks like the Find Function can only do find with multiple of 3 criteria. How can i get it to work? Thanks.

 

[Fluff]

Which line of code is highlighted if you click debug?

 

[Akuini]

Is there another way to do this? It looks like the Find Function can only do find with multiple of 3 criteria. How can i get it
to work? Thanks.

Not sure why that happened. Do you have another lines of code in the Sub?

Let's test the code I gave you:

Excel 2013 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	LetterA	LetterC	LetterE	LetterH	LetterL	LetterM	LetterN	LetterO
2​	1​	2​	3​	4​	5​	6​	7​	8​

Sheet: Sheet2

RESULT:

Excel 2013 32 bit

	A​	B​
1​	LetterM	LetterN
2​	6​	7​

Sheet: Sheet2

 

[Mike2]

Hi Fluff,
The code ran without an error, so no line of code got highlighted.
The macro just did not complete the job.

Hi Akuini,
Thanks for trying out the code, but it seems the code did not work on your test as well.
Or maybe i did not provide a clear information in my inquiry.

In my table of data, I have 20 columns that runs from A-T. Each column has header title as LetterA-LetterT.
My quest is to find a specific header title and delete the entire column off the table of data.
In my code, i tried to do a multiple find of specified header title, in this case there are 8
("LetterA", "LetterC", "LetterE", "LetterH", "LetterL", "LetterO", "LetterP", "LetterR") and delete the
entire column once they are found in the subroutine.

In my code as well as Akuini suggested code, the macro only found and deleted column with
header title of LetterA, LetterC, LetterE and LetterP, LetterR in the For loop and that's it...strange as it is.

I use Excel 2010...any idea why the code did not work?

Thanks.

 

[Akuini]

Not sure why that happened.
Maybe there's a trailing space in the headers that weren't deleted.

Try changing Lookat:=xlWhole with Lookat:=xlPart

 

[Mike2]

OMG that works !!! Wow, your assumption is correct Akuini.
Did not know Lookat:=xlPart will do the job for this case.

Thank you so much for your help Akuini

 

[Akuini]

OMG that works !!! Wow, your assumption is correct Akuini.
Did not know Lookat:=xlPart will do the job for this case.

Thank you so much for your help Akuini

You're welcome.

But you need to understand how Lookat:=xlPart works, it means that the code will search the word partially.
So say you have columns "LetterA", "LetterAB", "xLetterAxz", & you search by "LetterA", then all those 3 columns will be found ( and in this case, get deleted) not just column "LetterA".
So the best way is to delete all the trailing space first then run the code with Lookat:=xlWhole.

 

[Mike2]

Thank you Akuini. I will surely keep that info in mind for future reference.