delete entire row if not matched

**dumdumbum** · 07-04-2020, 05:52 PM

Hi,
I am looking for a macro that will do the things mentioned below
If column I of 1.xls matches with column B of sheet2 of 2.xlsx then dont do anything & if not matched then delete entire row
Plz see the sample file attached below
Thnx

Output of the macro will be
in 1.xls the only 2 row will be there 25 & 100
rest all data will be deleted in 1.xls
macro will be placed in macro.xlsm
all files are closed onl macro.xlsm is opened & all files are located in different place so the path will be hardcoded in the macro & i will edit it as per my requirement

**karmapala** · 07-05-2020, 04:14 AM

Maybe something like this ?

Please Login or Register  to view this content.

The code assumes that those two workbooks are already open.
It doesn't matter if the code resides in another workbook.

**dumdumbum** · 07-05-2020, 04:28 AM

Please Login or Register  to view this content.

I modified the code as per my needs
But getting error
run time error 1004
application defined or object defined error

highlighted line

Please Login or Register  to view this content.

**karmapala** · 07-05-2020, 05:22 AM

Very sorry, my bad.
I forget to put sh1. in the code

.

Please Login or Register  to view this content.

**dumdumbum** · 07-05-2020, 06:43 AM

Please Login or Register  to view this content.

This code is not working i modified it as u mentioned but getting same error

**karmapala** · 07-05-2020, 11:04 AM

2020-07-05_23-00-50.gif

In my side, it doesn't throw me an error anymore in this line :

Please Login or Register  to view this content.

Yet, once again my bad and sorry for that, I forget to put . in this code :

Please Login or Register  to view this content.

as it should be like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**dumdumbum** · 07-05-2020, 11:19 AM

Please Login or Register  to view this content.

Thnx For letting me know that i made a mistake

Please Login or Register  to view this content.

actually i corrected it

Please Login or Register  to view this content.

But Still i am getting issue

Plz see the sample pic

**karmapala** · 07-05-2020, 11:43 AM

That's strange, because (as you can see in the animation where I put the code in Book3), at my side it doesn't throw me an error,

Please try to check ... like this :

Please Login or Register  to view this content.

**dumdumbum** · 07-05-2020, 01:44 PM

No Problem Karmapala
i posted this question here https://eileenslounge.com/viewtopic.php?f=30&t=34920
Thnx For helping me in solving this problem

**dumdumbum** · 07-05-2020, 02:10 PM

Please Login or Register  to view this content.

Thnx Alot Karmapala for helping me in solving this problem
little error was there & its been corrected
Have a Great Day

**karmapala** · 07-05-2020, 06:20 PM

Originally Posted by dumdumbum

Thnx Alot Karmapala for helping me in solving this problem
little error was there & its been corrected
Have a Great Day

It's difficult for me because I use Excel 2010 which has more rows count.
Until before, I thought that just using the "Rows.Count", will work to every Excel version,
but it turn out I need to define the sheet "sh1.Rows.Count" in order it work to every Excel version.

Thanks for clarifying, dum.

btw, I just noticed this in your code :

Please Login or Register  to view this content.

Since you define variable tt to c2.value,
then I think better use it as follow :

Please Login or Register  to view this content.

Orrr....
maybe you put that tt line just to check whether it gives a correct value or not

.

delete entire row if not matched

LinkBack

Thread Tools

Rate This Thread

Display

delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Re: delete entire row if not matched

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA:Copy and Paste Entire Row of specific Part Number if it is matched into a New Workbook

Search the value in multiple sheets and display the entire row of matched value in list bo

[SOLVED] transfer every matched entrys entire to different-sheets (paste in cells)

[SOLVED] Transfer Every Matched Entry's Entire Row to a Different Sheet

Compare two lists and return matched values, including entire row data (Index/Match?)

[SOLVED] [How-to] ask marco to find, cut, paste entire row to new sheet if matched?

[SOLVED] Retrieving the Entire Row of a Matched Value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions