vba filter
 

Hi,

I have a table in excel that looks like this:

Book1 Author1 Categ1
Book2 Author2 Categ2
Book3 Author3 Categ3
Book4 Author4 Categ4

I also have a list : Book1, Book2

I want to write a vba code that deletes the entire lines of those
items in my table that are not apperearing on my list. Can anyone help
me?

Thank you
Gabriel

vba filter
 

Gabriel
I'll assume your list is on Sheet "Two" and your data is on sheet "One".
Sub DeleteRows()
Dim TheRange As Range
Dim c As Long
With Sheets("One")
Set TheRange = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
For c = TheRange.Count To 1 Step -1
If Range("TheList").Find(What:=TheRange(c), _
LookAt:=xlWhole) Is Nothing Then _
TheRange(c).EntireRow.Delete
Next
End With
End Sub

HTH Otto
"Gabriel" wrote in message
om...
Hi,

I have a table in excel that looks like this:

Book1 Author1 Categ1
Book2 Author2 Categ2
Book3 Author3 Categ3
Book4 Author4 Categ4

I also have a list : Book1, Book2

I want to write a vba code that deletes the entire lines of those
items in my table that are not apperearing on my list. Can anyone help
me?

Thank you
Gabriel

vba filter
 

If you have headings for your lists:

BookName AuthorName CatName
Book1 Author1 Categ1
Book2 Author2 Categ2
Book3 Author3 Categ3
Book4 Author4 Categ4

And your criteria list uses the same heading:

BookName
Book1
book2

And you assign the name "database" to all of the main list, including
headers

And you assign the name "criteria" to the criteria list, including the
heading,

then you can use this macro:

Sub DataDelete()
ExecuteExcel4Macro "data.delete()"
End Sub


This uses an old Excel 4 technique that is no longer part of the Excel user
interface but is still supported beneath the surface.

--
Jim Rech
Excel MVP

vba filter
 

How would the Sub DeleteRows() would change if I had two lists, for
instance Book1 or Author2. In other words, all lines in my table should
be deleted if they not contain either Author1 or Book2.

Thank you in advance
Gabriel


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

vba filter
 

Gabriel
Let me see if I have this straight. You have a table of Book 1. etc and
Author 1 etc, and Categ 1. We can ignore the categ column.
You also have a list of books and a list of authors. You want to delete
any row of the table if its Book or if its Author is not on the respective
list. In other words, if we find a match with just one list, Book or
Author, the row stays. Is that right?
The following macro does that. The conditions a
The book list is named "BookList"
The author list is named "AuthorList"
Both the "BookList" and the "AuthorList" are on the same sheet,
and that sheet is the active sheet.
Your Table is on the "Table" sheet with the Books in Column A and the
Authors in Column B.
HTH Otto
Sub DeleteRows()
Dim TheRange As Range 'The rng of Column A of the Table sheet
Dim c As Long
Dim TheLists As Range
With Sheets("Table")
Set TheRange = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set TheLists = Union(Range("BookList"), Range("AuthorList"))
For c = TheRange.Count To 1 Step -1
If TheLists.Find(What:=TheRange(c), _
LookAt:=xlWhole) Is Nothing Then
If TheLists.Find(What:=TheRange(c).Offset(, 1), _
LookAt:=xlWhole) Is Nothing Then _
TheRange(c).EntireRow.Delete
End If
Next
End With
End Sub
"Gabriel Matache" wrote in message
...

How would the Sub DeleteRows() would change if I had two lists, for
instance Book1 or Author2. In other words, all lines in my table should
be deleted if they not contain either Author1 or Book2.

Thank you in advance
Gabriel


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!