Match and delete row with double click

Hi all

Could you please help as I'm a little stuck. Any help is greatly appreciated.

Im looking for a way to delete an entire from sheet 1&2 with a double click based on criteria matching a cell on sheet 1.

For example if I double click on sheet1 C2 the code would match the text in sheet1 H1 with sheet2 D2 and then delete the entire row along with the matching row in sheet1.

I hope this makes sense.

I have uploaded the s/sheet.

Thanks in advance


Test v1.xls

Originally Posted by jaslake

Hi johnny_p

Perhaps something like this

Please Login or Register  to view this content.

Hi

Thank you for sorting this for me. I does work however it takes about 30secs+ to run through the code.

Is there a way of making it quicker?

Thanks again

John

Originally Posted by jaslake

Hi johnny_p

The Code runs instantaneously on my platform.

You might try deleting the extraneous formatting...you have an awfully large file (900+ KB) with NO DATA!

You may wish to try this

Please Login or Register  to view this content.

Thanks again for your support with this.

I have tried your new code and the same happens again. I do have 10,000 line for it to search through. Will this cause the delay?

Hi Jaslake

I have two sheets. The first which is the summary (sheet1). The second (sheet2) holds more detail and can contain up to 20 rows which relate to a single row in sheet1. They are identified by using the same code in sheet1 H1 and sheet2 D2

When I review the summary I want to be able to delete the row in sheet1 along with all the related rows in sheet2. This will save having to go search and delete the rows.

Hope this helps?

Thanks again for your efforts.

John

The data starts on row 2 in each sheets.

Sheet2 will have approx 10k rows.

The sheet I uploaded is the same one I will using.

Sorry, I clearly under estimated the expected rows.

I cant add the data as it will be sensitive. It will be approx 10 rows across.

Hi Jaslake

I have spent sometime looking into why this takes so long in my live workbook.

I have quite a few columns with countifs, and index & match functions. In your latest code I see that you turn off the updating functions which does cut the execution time from 60 secs to 30 secs. I have done some thorough testing on both versions and for some reason it only deletes the first match it finds on sheet2 and then continues on.

This may be the to do with the identifying cell/column (D:D) in sheet2 having a Index/Match function. This connects to sheet1.

I can avoid this by changing the double click code to on to Sheet1 Col F. I cant however get it if to look for a match on Sheet2 Col C. Would this be to do with the offset?

I have played around with the autofilters to see if there was some kind of speed improvement, however with all the functions I have there is still quite a delay.

I may have to settle for the sub 30 sec delay on each execution due to the many functions within the workbook.

Thanks again for your help and patience with this.

I hope this makes sense?

John

Thanks Jaslake

Your a star. It seems to be working, altough still takes apporx 30 secs. I dont think this can be helped due to the many functions held within the sheets.

I will undertake some more testing in the coming days and get back to you.

Thanks again

john