if cell contains value, delete whole row

[Armitage2k]

Hello!

I am writing on a macro to get some documents in order, but need some assistance in cleaning up please.

I would like to have a macro which deletes me the whole ROW if the cell in this row DOES NOT contain the words "V1", "V2" or "V3" (without "). ATTENTION! The cell does not only contain V1, V2, V3, it contains several words and these might be one of them, hence I need the macro the check the cells content for these values.

Example:

Column A
1. I want a V1 setup
2. I want a hamburger
3. I want a V5 setup
4. I need a dog

The macro should delete rows 2,3,4, only row one remains as it is.


However, I would need to be able to define the range of the checked cells, since I dont want to check the whole document, merely a part of it.

thanks,
A2k

[Leith Ross]

Hello Armitage2k,

This macro will check the cells "C20:C30" on "Sheet1" for V1, V2, or V3 in them. If not the entire row will be deleted.

Please Login or Register  to view this content.

[Armitage2k]

Thanks for the help, but unfortunately the function "CreateaObject" is not available in office 2003. I always get the error sub or function not defined...

Is there any other way to get this to work?

Thanks,
A2k

[shg]

Try this:

Please Login or Register  to view this content.

In Leith's code, CreateaObject should be CreateObject

[Armitage2k]

Ahm, this one doest not work at all. it directly deletes me every cell containing V[123] instead of deleting every row which cell is NOT including this value.

I changed leith's code from GetaObject to GetObject and now get an error complaining that

Please Login or Register  to view this content.

has a type mismatch.

An ideas...?
Thanks,
A2k

[Leith Ross]

Hello A2k,

My mistake. That should be CreateObject.

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

[Armitage2k]

I know about the GetObject typo and I already corrected it in the first place. However, even with the correction I get the above mentioned error (which does not have to do with this typo):

Type mismatch

Please Login or Register  to view this content.

Regards,
A2k

[Leith Ross]

Hello A2K,

You need to use CreateObject not GetObject. Then it will work. If you still have problems then post your workbook.

[Armitage2k]

I have made the GetObject adjustment already a little while back as mentioned in my post above. however, the code still does not seem to work...

Here an attached workbook.
FYI, I have 2 macros working in this book. First macro is copying all relevant data from another sheet to the VIP sheet and formats it properly.

the seconde macro is the one I am having struggles with. this one should check column K3:K300 for any V1,V2,V3 entries, and if these are not present, delete the whole row. Result should be that only entries with a V1+2+3 in column K are left on the sheet.

Thanks for all the help,
A2k

[Leith Ross]

Hello Armitage2K,

The workbook is fixed. You had a few entries that were generating errors. After seeing the data layout, I expanded the macro to cover all situations. Here is the macro code. This change has already been made to the attached workbook.

Please Login or Register  to view this content.

[Armitage2k]

and the only thing left to do:

mark as solved and hail to Leith Ross :D :D :D

thanks for all the help. works like a charm now.

Regards,
A2k

[nojrellum]

Hey Leith, question on this one for you. I am trying to apply it to my worksheet as well.

My range is AY1:AY46000
The values in that column are 6 digits, like 123456, 123457, 123458 and so on.
I want to keep all that start with 5013, 5014, and 5015.

I won't include the abomination of a code I tried to work with here, but I'm guessing the one you made above and the one I need is similar? Your guidance is much appreciated!
Thanks again