delete rows if matching data not found on another worksheet

[ssujatha7]

The comparison needs to be done on unique id between sheet 1 and sheet 2. Both the sheet have id as a column. Need to compare this column. And delete the row in sheet 2 if there is no row for a particular id in sheet 1.

Thanks!

[:) Sixthsense :)]

Use countif() formula in a column and match the data with another sheet and apply filter and select 0 result and delete it

[Marc L]

Hi,

read VBA help of Find method and you can start to use Macro Recorder !

Edit : too late, a guru yet answered …

[ssujatha7]

thanks! I actually want a VBA code. Working on a Macro.

[ssujatha7]

I'm new to VBA and unable o figure out what the code should be . can anyone help me with the code. I read up about Find method but not able to delete the rows that don't match.

[:) Sixthsense :)]

We need a sample workbook for giving exact vba solution

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ssujatha7]

Have uploaded a sample file. the objective is to delete the rows 2,7 and 8 from TCExecution worksheet as these test ids are not present in TCDetails Worksheet.

Thanks for all your help!!!

[:) Sixthsense :)]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[ssujatha7]

when I run the macro it is opening the dialog box to select a file. and if click cancel, I get an error message and then a column with 1 is added. I already have the file open and with a few macros in it.

[:) Sixthsense :)]

Two things you should remember while running the macro...

1) You have to run it from the sheet on which the deletion needs to be done.

2) Check the Comparison sheetname (i.e.) in the sample file the you named it as TCDetails so check whether any sheet exist with that name. If not then the macro will throw errors like this....

If your name of the comparison sheet is different then find this word TCDetails in the provided macro and replace it with your actual sheet name

[ssujatha7]

the sheet name is the same and ran the macro from the sheet TCExecution where the deletion needs to be done. still getting this issue.

[:) Sixthsense :)]

I added the code in your sample file and attaching the file for your reference. Run the code from the Sheet2 sheet.