Delete Certain Rows In Excel Based On Criteria

[dci9497]

Hello everyone,

First off, let me thank you for taking the time to answer so many questions! I don't know what I'd do without people such as yourselves.

Also, this is my first time posting; I've read the rules and guidelines, but if there is something I'm missing or did incorrectly, please pardon my ignorance.

On to the question:

I'm looking for VB code to delete rows only rows that I have specified. The data I'm trying to sort through contains user marks for people. Each user mark is three characters long and includes text and numbers.

There are about 200 rows in the sheet. I'd like to delete all the rows that do not list the user marks I have identified.

For example:

A1 234
A2 598
A3 tol
A4 rlv
A5 fks
A6 re3

This data above would be the original, without deleting. The using a macro/VB code to delete what I've specified
would then filter to:

A1 234
A2 tol
A3 re3

I hope I have explained my question clearly enough. I'm relatively new to VB and don't know where to begin in contructing the code for this.

I'm using Win XP and MS Office 2003.

If there are any details I have left out please let me know and I'll provide them to you immediately.

Thanks again for all your help!

dci9497

 

[Joe4]

Welcome to the Board!

Where are you indicating the user marks you would like to keep?
If you have a list of those, you could use a "helper" column with a VLOOKUP formula to look to see if each entry appears in your list. Then you could delete all the ones out that don't (via Filters, sorting, or VBA).

 

[Alphacsulb]

I agree, use a helper column to identify the criteria with maybe an X or something. Then use a script like this one below.

In this example, the helper column is AV. The criteria is OVRSE.

Sub Test()
ActiveSheet.AutoFilterMode = False
With Intersect(Range("AV:AV"), ActiveSheet.UsedRange)
    .AutoFilter Field:=1, Criteria1:="OVRSE"
    .Offset(1).EntireRow.delete
    .AutoFilter
End With
End Sub

 

[dci9497]

I've never used the VLOOKUP so I'll have to research that further. Or could you briefly describe how that works?

Basically, I'm pulling reports from a data warehouse every day and in these reports is a bunch of data that I don't need.

The process I'm using at the moment is going through sheet highlighting the user marks I want to keep. Then, once the 15-20 (depending on the report) have been highlighted, I'll go through and delete all the rows that are not highlighted.

It can be quite tedious.

Since I have to do this on a daily basis, I thought maybe a VB script would be the most efficient way to simply delete all the rows I didn't want, using the list of user marks I need as criteria for the delete.

Will using a filter or sort method delete or only hide the irrelevant data? I'd preferably like to delete.

 

[Alphacsulb]

Here is an example of Vlookup. This version of the VLOOKUP is more complex because I didn't want to see the any error signs in there. But you get the idea.

Excel Workbook
	A	B	C	D	E
1				Formula says Look for this value:	And Put this on in there if you find it:
2				12	X
3				34	X
4				56	X
5				78	X
6				90	X
7
8	Your Data:	Vlookup Formula:
9	12	X
10	56	X
11	65
12	59
13	21
14	21
15	12	X
16	58
17	56	X
18	90	X
19	12	X
20	15
21	47
22	54
23	65
24	34	X
Sheet1

 

[dci9497]

Hmm, this certainly does look helpful!

Let me explain more about my situation and see if I can clarify my objectives.

I'm pulling new reports every day from a data warehouse. These reports can then be manipulated in Excel. This is where all the tedious work is done (see previous post).

Since I'm starting with a brand new set of data (save for the user marks) each day, which method (vlookup or vb script) would be more time saving?

 

[Alphacsulb]

How many different type of products do you have?

 

[dci9497]

On any given day there will be about 200-220 rows in the report. From there I'll delete down to 13-29 rows depending on the report.

 

[Alphacsulb]

So your saying you have 29 different products? The ones you want to keep vary daily?
Do you want to delete the duplicate ones, or keep them? Is there any other info on columns that go along with the product?

I have a few ideas that could work for you.

 

[dci9497]

The data I'm pulling pertains to individuals in a company. Each individual has a unique identifier (user mark). When I pull the reports each day, there are miscellaneous user marks that are not needed for the report; these marks are either employees that are no longer with the company or are from other regions.

Each report includes three different regions (200-220 rows). I'll then have to filter (delete) the data that does not correspond to the specific region I'm filtering for.

For example:

The report is pulled and it contains 200 rows. Then I have a list of people (user marks) for Region 1 to use as reference. The user marks for Region 1 are aaa, bbb, ccc, ddd...etc. I then go through the sheet highlighting aaa, bbb, ccc, ddd...etc, until I've highlighted all of that region's respective user marks. Then I delete the unwanted rows (miscellaneous rows) leaving me with only the specific rows I've highlighted.

I hope I haven't confused you and that makes sense. It's difficult to explain this process.