VBA: How do I find data across a workbook to delete it?

9lb - go to http://www.thecodenet.com/downloads/Search.zip and look at the code - it has some stuff in there that will probably do what you want.

Kentman,

Thanks for the link. I've had a look and the Example 6 looks like the most relevant, however I can't get it to work with my workbook.

Also, it only looks up the keyword (in this case, the Status "Closed"), but I then need the macro to store the ID every time a result of "Closed" comes up, and then run through all the other worksheets and delete the rows that contain those IDs... Can't see how to do that with the provided link.

Cheers!

Would need more information to assess how feasible this is (for a free forum like this). Here is an initial list of questions. There may be more later.

1. What is the name of the 'first sheet' that we have to monitor for Status changing to 'Closed'?

2. On that 'first sheet', which column is the 'Status' column?

3. How does the 'Status' get changed to 'Closed'?
Is that a manual entry? A formula result? An entry made by other vba code?

3. Do all sheets, including this 'first sheet' have the ID in the same column? If so, which column? If not, more details please.

4. Do all the worksheets have a heading row? Row 1?

Hi Peter,

Thanks for the reply. Here are the questions in reply:

1. Name of first sheet is "MAIN";

2. The "Status" column is column V;

3. As I mentioned previously, this is an Excel export from another programme. The status gets changed manually in the other programme, so there would be no altering of the field in Excel;

4. Yes, all sheets have the ID column in column A and all sheets have cell A1 titled "ID";

5. Yes, all sheets have row 1 as a heading row, though the columns (apart from column A) are different for each sheet.

Cheers!

Test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteClosed()<br>    <SPAN style="color:#00007F">Dim</SPAN> rClosed <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> sID <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("MAIN")<br>        <SPAN style="color:#00007F">Set</SPAN> rClosed = .Columns("V").Find(What:="Closed", LookIn:=xlValues, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rClosed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>            sID = .Cells(rClosed.Row, 1).Value<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>                <SPAN style="color:#00007F">With</SPAN> ws.UsedRange<br>                        .Columns(1).AutoFilter Field:=1, Criteria1:=sID<br>                        .Offset(1).EntireRow.Delete<br>                        .AutoFilter<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> ws<br>            <SPAN style="color:#00007F">Set</SPAN> rClosed = .Columns("V").FindNext<br>        <SPAN style="color:#00007F">Loop</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>