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

[9littlebees]

Hello everyone,

I need help with a workbook which is a data export from another programme. I'm using Excel 2000.

I have a fairly extensive VBA macro embedded into this file already to tidy it up and want to add something else to it. However, I can't for the life of me think of how to do it and haven't had much luck on the forum (not sure what to search for).

It has to do with looking up the Status of all the IDs on the first sheet, and when the Status for any ID is "Closed", any field (row) containing that ID across the entire workbook will be deleted. This means that I am left with a workbook that only contains "Open" data.

The problem is that the Status column is only on the first worksheet (there are seven in total), so I'm not sure how a Find function would help/work...

This sounds like it should be a relatively straightforward job, but as I've said, I'm a bit stuck, so please help!

Cheers!

 

[Kentman]

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.

 

[9littlebees]

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!

 

[Peter_SSs]

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?

 

[9littlebees]

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!

 

[Peter_SSs]

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>