Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count rows based on multiple criteria | Excel Worksheet Functions | |||
Delete rows based on multiple criteria | Excel Discussion (Misc queries) | |||
Count rows based on multiple criteria | Excel Worksheet Functions | |||
Deleting rows based on cell criteria | Excel Programming | |||
Deleting rows based on criteria | Excel Programming |