How to create a macro that will delete all rows NOT containing specified numbers

**elmaco12** · 07-03-2008, 04:41 AM

Hello,
This is my first post on this forum, so I would just like to say hi to everyone!

What I need some help with is to create a macro that will seek through a specified column, looking for a couple of specified values. If a cell i that column does not contain any of the specified values, the macro should delete the whole row.

It would be awsome if it was possible to create an InputBox wich asks the user what column to look in and for wich values to look for.

(i have managed to create a macro that works for only one value, but not for multiply. I have about 36 values to enter)

Thanks for your time!

/M

**royUK** · 07-03-2008, 05:52 AM

Welcome to the Forum. It's always best to post your existing code, but you must use Code Tags - see the link below.

A search of the Forum will find numerous examples of this kind of code.

**royUK** · 07-03-2008, 06:07 AM

This code will prompt for a Column to search & a text to search for

Please Login or Register  to view this content.

**elmaco12** · 07-03-2008, 06:40 AM

I tried searching but i could not find anything that solved my problem.

However the code that you posted deletes the values that you enter in the box, i need the opposite. I need the macro to ask me for a bunch of numbers to look for, and then delete all BUT those numbers.

Also it would be great if i could enter more then one value in the Inputbox.

Say that i would like to keep the numbers 1,3,5,6,21,24...(etc).. then i would want to enter these numbers in the box, and then have the macro delete all others... hmm.. hope you understand what i mean

EDIT:
I found a way to edit the code so that istead of deleting the one you enter, the macro deletes all other values. But how can I enter more then one number in the InputBox

**royUK** · 07-03-2008, 07:01 AM

You can make the code delete any values different to the input value by changing it slightly like this

Please Login or Register  to view this content.

To use an input bos to get multiple values wouldn't be as easy. You would have to rely on the user inputting correctly with commas, etc. Then split the input into a list to search for

**elmaco12** · 07-03-2008, 07:12 AM

Okey, but i really need to be able to keep more then one number. Maybe it's possible to do it another way. Let me give you some background:

I get these hugh documents with about 5000 rows and about 200 different departmentnumbers.

So what i want to do is to, some how, find a way to delete all the departmens that i'm not interested in.

The way I solved it before was that i "hard coded" the departmentnumbers and used an autofilter to show all the ones that i was intrested in, then copy them to a new sheet and then delete the old sheet.

But that leaves me with the fact that if i want to change departments i have to edit the code, and if i send it out too my colleagues then they will definatly get it all wrong.

How to create a macro that will delete all rows NOT containing specified numbers

LinkBack

Thread Tools

Rate This Thread

Display

How to create a macro that will delete all rows NOT containing specified numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions