Excel delete records with macro

[chrisp909]

I am not much of an excel user.

I have a list of 4 months worth of cell phone numbers. I am looking to pick out all unknown numbers. I would like to type in known numbers once and have the record completely purged from the list. A line delete.

In other words.
I want to be able to type in a string from a particular field and completly delete all reocrds with that string with one command.

Any simple way of automating this?

 

[fredericofonseca]

Without going to code you can use autofilter for that.

Assuming for example the following.

Cell a1 = phones
Call b1 = Dates

Heading on first line.

Select the first line cells (a1 + b1)
go to menu->data->filter->autofilter

You will notice that you have now two drop-down buttons on both A1 and B1.

If you hit on the one on A1 you will be shown a list of all the number on your list.
Select the one you wish to delete.
You can now select all the lines shown by using the following steps.
1- Position on cell a2.
2 - hit shift+ctrl+end
3 - Hit delete

All those number are deleted now.
After deleting all the number it is advisable to remove the filter again, and sort the data, as the deleted lines will be there, but just blanked.

As an alternative you can replace 2 and 3 with a selection of the full row, and then a delete row instead.


If this is enough for you fine, otherwise some VB code will be needed.
It will be a mixture of a loop with a find, select row/range and delete selection (Range.Delete (xlUp))

tell us which you prefer.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DaleWatson123321]

chrisp909,

Based on your description, I've developed a model that automates the process - where all you need to do is enter your phone number into a cell and click an adjacent button.

The result is that all records with that same phone number are then deleted from your database.

If you would like the model, email me and I'll send it via return email. If anyone else can also use the model, please feel free to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[carrr]

John Walkenbach offers a free "row delete" add-in on his web page which should give you what you need:

http://www.j-walk.com/ss/excel/files/rowdel.htm

 

[DaleWatson123321]

Chris,

While I would encourage anyone to explore and utilize the offerings of John Walkenbach, please appreciate that the model I developed is "specific" to your needs.

In addition, you should appreciate that by using such "specific" models (and learning how they're set up), you'll end up being able to develop your own "custom" applications - that can have more flexibility and options - for example ones that are "completely automated" for your specific needs, instead of having to pick from a menu of options.

Excel's "Advanced Filter" component is VERY powerful indeed, and should be considered by anyone and everyone who is dealing with "spreadsheet-based" databases.

I hope this "background info" proves useful.

As mentioned, the file I developed is yours for the asking. And I also have other "example files" on the use of the Advanced Filter - once you're ready for "the next step" in your learning curve.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca