Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting various rows of cell data throughout the master list tha.
I have an ~30,000 row master list, which contains 3,000 (noncontiguous) rows
of false information. I have compiled a list of the false data on a separate worksheet. How do I efficiently match the two lists and delete the corresponding false information from the master? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting various rows of cell data throughout the master list tha.
Try this
From http://www.rondebruin.nl/delete.htm Example with the criteria on a different sheet The example below filter A1:A? In a sheet named "Sheet1" Note: A1 is the header cell And use as criteria all the cells in column A In a sheet named "Criteria". Note:You can use also wildcards like *food* or *store if you want Sub Delete_with_Autofilter_More_Criteria() Dim rng As Range Dim cell As Range Dim Criteriarng As Range Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With Sheets("Criteria") Set Criteriarng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp)) End With For Each cell In Criteriarng With Sheets("Sheet1") .Range("A1", .Cells(Rows.Count, "A").End(xlUp)) _ .AutoFilter Field:=1, Criteria1:=cell.Value With .AutoFilter.Range Set rng = Nothing On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With Next cell With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Flip" wrote in message ... I have an ~30,000 row master list, which contains 3,000 (noncontiguous) rows of false information. I have compiled a list of the false data on a separate worksheet. How do I efficiently match the two lists and delete the corresponding false information from the master? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Deleting Rows Automatically using a Text File List | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |