LinkBack URL
About LinkBacks
I want a macro to look at column D on sheet 1 from row 2 all the way down to the last used row and if for example column D row X is found in sheet2 column D then delete the entire row in sheet1.
I want a macro to look at column D on sheet 1 from row 2 all the way down to the last used row and if for example column D row X is found in sheet2 column D then delete the entire row in sheet1.
I have the following which I think is along the right lines but doesn't work
Please Login or Register to view this content.
Last edited by ScabbyDog; 03-06-2013 at 02:57 PM.
This is your code, you posted it a while back and I happened to comment on that thread. The match function is much faster than the "Find" function. I have tested it and it works.
Please Login or Register to view this content.
Ah yes didn't realise I could use that. Tested it but it seems to stop when a match is found so I need to run it about 15 times in a row for it to match and delete everything. Is there a smart way to loop the above? I just used For x = 1 to 15, Next x and it does the trick.
Last edited by ScabbyDog; 03-06-2013 at 04:00 PM.
I think you need to change this in red from a to d
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
If there is data in column D whatever row it is there will definitely also be data in column A the same row. So would the above fix still apply Mike?
Well this is looping thru columns A:D. I thought you only wanted to look in column D. So changing A to D will speed up your code. Otherwise your Looping A2,B2,C2,D2,A3,B3 Etc...
Please Login or Register to view this content.
Sorry, It was my mistake, as Mike said you could use
This should loop through each cell in D2 all the way down as long as there are data in column A. You could use column D for LR as long as column A and D have the same length.Please Login or Register to view this content.
Last question,
How can I alter it so that when a match is found instead of c.EntireRow.Delete it replaces that cell with the value of the cell directly to the right of where the match was found in sheet2?
Please Login or Register to view this content.
Would this be along the right lines;
Please Login or Register to view this content.
The above isn't quite working for me but I think I'm close. So if sheet 1 D5 for example matches sheet2 d55 then I want what is in sheet2 e55 pasted into sheet1 d5
Is the below any closer? It runs without error but doesn't do as intended.
Please Login or Register to view this content.
Solved it, just offsetting issues
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks