Delete rows without a specific value in a cell

**chriss** · 06-02-2008, 08:05 AM

Hi guys,

I have a problem that I hope you can help me with!

I have 2 spreadsheets of delivery information. Sheet 1 contains data of all stores in the UK with addresses. Sheet 2 containts contacts of all stores that promotional matrial needs to be sent to.

The only common cell in a column of 4 digit store numbers.

I need to delete all rows from sheet 1 that do not contain a value in the store number column that is displayed on sheet 2.

Hope that makes sense.

ps

for forum moderators or regular posters, where does your knowledge of Excel come from? Is it experience, or are there books / courses you can reccommend?

Many thanks,

Chris

**royUK** · 06-02-2008, 08:41 AM

Something like this

Please Login or Register  to view this content.

There are numerous books & courses, I learn by experience - posting on Forums & making my own projects work.l

**chriss** · 06-02-2008, 09:32 AM

Hi Roy,

Thanks for you response.

I think you overestimate my understanding of macros in Excel!!

If the common cells are in column E, what would the code be?

I apologise for the ignorance, but when it comes to coding it might as well be written in ancient Greek backwards!!

Thanks!

Chris

**royUK** · 06-02-2008, 09:48 AM

Try this, but use a copy of your workbook

Please Login or Register  to view this content.

**chriss** · 06-03-2008, 05:01 AM

Thanks again Ry,

I'm sure it's just me but the macro didn't work for me. I hadn't given you this infomration before, don't know if it's relevant.

Sheet 1 contains 276 rows of data. The column headings are:
a = Territory / b = area / c = category / d = store number / e = stre number / f = address / g = telephone number / h = speed dial / i = fax / j = email address / k = store email / l = opening hours.

Sheet 2 contains 96 rows of data. The column headings are:
a = first name / b = surname / c = email / d = postcode / e = store number

The only common column is "store number". I want to finish with 96 rows of data on sheet 1 that marry up to the sotre number on sheet 2, sa they hav relevant elivery information, and I want to perform a mail merge.

Thanks again!

Chris

**SOS** · 06-03-2008, 06:21 AM

Hi Chriss,

Do your sheets have header rows in them?

**chriss** · 06-03-2008, 12:02 PM

Hi SOS,

They do indeed have headers. Will this bve a problem?

I apologise again for my ignorance in this area!!!

Chris

**royUK** · 06-03-2008, 12:27 PM

The code that i gave you will only work if yo have those specific sheets. Can you add a zipped example workbook

**chriss** · 06-03-2008, 06:34 PM

I have attached an example of the spreadsheet. These contain the column headings as they appear, but I have deleted the rows as they contain contact details (numbers, email addresses etc.).

I have added 1 row of data entry on each sheet. Will this be ok? As I mentioned there will actually be 276 rows of entry in sheet 1 and 97 rows o entry in sheet 2.

Thanks for your continued help!