Deleting Rows based on matching values in column A, in two different worksheets

[Insert Name]

I am comparing column A of two different worksheets. I want keep all of the column A values that are in both sheets. I want to delete the rows of those values in sheet2 that do no appear in sheet1. Can someone please help?

Thank you!

[mike7952]

This should do it

Please Login or Register  to view this content.

[mike7952]

This maybe faster

Please Login or Register  to view this content.

[xladept]

Hi,

Try this - what do you want to do about duplicates in Sheet2??

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code into the white space on the right

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[Insert Name]

Hi,

Try this - what do you want to do about duplicates in Sheet2??

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code into the white space on the right

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

There should not be duplicates in the sheet I use this in with. Does sheet2 have duplicates?

[Insert Name]

If I wanted to do this for two excell documents, the first of which has 20 tabs, the second has 23. How would I do this?
If sheet1 has the list to compare from, and every sheet after that has the column A to delete rows from.

[mike7952]

This should do it

Please Login or Register  to view this content.

[xladept]

If I wanted to do this for two excell documents, the first of which has 20 tabs, the second has 23. How would I do this?
If sheet1 has the list to compare from, and every sheet after that has the column A to delete rows from.

You need to have the Excel Documents Active then just play this:

Please Login or Register  to view this content.

[Insert Name]

How do you make excel document active?

[xladept]

Have the document "up" the active one is the one that comes up when you click on Excel. In other words - while your looking at the Workbook in question - the one with 20 or 23 tabs! The code doesn't need to be in the active book but it must be in the module of a macro-enabled book (for 2007 and 2010 which you have).

[mike7952]

Will the list be in both workbooks? In other words will each workbook have its own list to ompare with he other sheets?

[Insert Name]

Yes, but I will only open one of the excel documents at a time is this ok?

[Insert Name]

In the book I attached column A has our numbers in the book I am working with column A has seven numbers does this make a difference?
I should add that when I run the macros many of the unwanted cells are deleted, and those in sheet one column A do appear in other sheets. But some that do not appear in column A sheet one say in the other sheets. I think it might have to do with the numbers of each value in column A.

Thoughts.

Overall seems to be working well.

[mike7952]

Pace this in a separate workbook. The workbooks that your wanting to remove rows from must be opened before running macro.

Also change this part of the code to the names of your workooks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Insert Name]

If the last column in some rows are different does it matter? I want to see if I can get this to work for a similar situations with data. Where the number of column changes?

[mike7952]

Nope that wont matter

[xladept]

Well, when I ran it Sheet2 had more rows than Sheet1 - I assumed that they were duplicates since I traversed every cell in Sheet2 Row A???????