Deleting rows based on multiple criteria
 

Hi,

I have been through a couple of similar questions on the website, but
couldn't find exactly what I require.

I have a listing running into thousands of lines.

The data listing is for all the countries in the world. I require a
macro which would delete the countries( complete rows ) other than
those provided within the macro. The exception list would be listed in
the macro for eg, Country 1, Country 2, country 3 etc...

Appreciate some one's help on this.

Regards
Sandip

Deleting rows based on multiple criteria
 

Sandip,

It would depend upon the conditions. If you can construct a formula to
identify the rows to be deleted, you could use this technique


http://www.xldynamic.com/source/xld.Deletingv3.html
Deleting Rows/Columns in VBA - Autofilter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sandip Shah" wrote in message
om...
Hi,

I have been through a couple of similar questions on the website, but
couldn't find exactly what I require.

I have a listing running into thousands of lines.

The data listing is for all the countries in the world. I require a
macro which would delete the countries( complete rows ) other than
those provided within the macro. The exception list would be listed in
the macro for eg, Country 1, Country 2, country 3 etc...

Appreciate some one's help on this.

Regards
Sandip

Deleting rows based on multiple criteria
 

Hi Sandip,

Try :

Sub DeleteAllExcept()
Dim Exceptions As Variant
Dim cell As Range, LastCell As Range
Dim MyDelRng As Range
Dim WB As Workbook, SH As Worksheet
Dim CountryCol As String
Dim blKeep As Boolean

Exceptions = Array("Peru", "Brazil", "China") '<==== CHANGE
Set WB = Workbooks("MyBook.xls") '<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<==== CHANGE
CountryCol = "B" '<==== CHANGE

Set LastCell = SH.Cells(Rows.Count, CountryCol).End(xlUp)
For Each cell In Range(SH.Cells(1, CountryCol), LastCell)
blKeep = False
On Error Resume Next
blKeep = Application.Match(cell.Value, Exceptions, 0)
On Error Resume Next

If Not blKeep Then
If MyDelRng Is Nothing Then
Set MyDelRng = cell
Else
Set MyDelRng = Union(cell, MyDelRng)
End If
End If
Next cell

If Not MyDelRng Is Nothing Then
MyDelRng.EntireRow.Delete
Else
MsgBox "No data found to delete"
End If

End Sub


Change the four marked lines at the start of the sub to reflect your
exception countries, workbook and sheet names and the data column containing
the country names.


---
Regards,
Norman


"Sandip Shah" wrote in message
om...
Hi,

I have been through a couple of similar questions on the website, but
couldn't find exactly what I require.

I have a listing running into thousands of lines.

The data listing is for all the countries in the world. I require a
macro which would delete the countries( complete rows ) other than
those provided within the macro. The exception list would be listed in
the macro for eg, Country 1, Country 2, country 3 etc...

Appreciate some one's help on this.

Regards
Sandip

Deleting rows based on multiple criteria
 

Use an advanced filter to copy the data from the countries you want to
retain to another worksheet. (data=Filter=Advanced filter). You would
just list those countries in the criteria range.

You can then delete or retain your original data.

--
Regards,
Tom Ogilvy


"Sandip Shah" wrote in message
om...
Hi,

I have been through a couple of similar questions on the website, but
couldn't find exactly what I require.

I have a listing running into thousands of lines.

The data listing is for all the countries in the world. I require a
macro which would delete the countries( complete rows ) other than
those provided within the macro. The exception list would be listed in
the macro for eg, Country 1, Country 2, country 3 etc...

Appreciate some one's help on this.

Regards
Sandip