Loop to delete rows on another tab and run until blank cell is met.

[SillySox]

I am trying to create a macro that will run for each populated cell after cell "A8". The number of iterations will vary. The object is for the macro to run if there is something in cell "A9" - find the text that is in cell "B9" in another tab and then DELETE the entire row in that other tab. Then to do the same for A10, A11 et until a blank row in column "A" is met.

The complicating factor is that SOMETIMES the corresponding "B" cell may be blank and we dont want all of the empty rows in the other tab to be deleted. (not sure if that would happen anyway)

Data may look like this.

Particulars "A8"	Topping "B8"
Banana	Chocolate
Apple	Cinnamon
Pear	'blank'
Orange	'blank'
Pineapple	Strawberry

so basically the macro would run each time a value is found in "A" and would delete the row in another tab that contained the value in "B" ignoring the 'blank' items.

I hope i havent made this too confusing

thanks in advance
Quinn

[patel45]

attach a sample file with current sheets and desired result

[Andrew-R]

Something like this - replace the sheet names and the columns with your own:

Please Login or Register  to view this content.

[SillySox]

thanks Andrew! you truly are a legend!

One final thing - the spreadsheet this is working on contains product lines that have had base codes changed for various reasons and this part of the spreadsheet was to remove/delete the codes that relate to the same items but were earlier codes. Your loop works very well - but we have found that we can have 4 or more codes that relate to the 'master' code in col "A"

The data comes to us with the 'master' code in col "A" - old code 1 in "B", old code 2 in "C" and so on. How can I get this loop to run for codes in Col"B" in respect to "A" and then in col"C" in respect to "A" and so on.

Apologies if I am being stupid here - but I am new to this.

thanks for your help to date Andrew - it is HUGELY appreciated - I think by MANY MANY others on this site as well.

cheers

ps - I did try changing sSearchString from B:B to C:C thinking it was an easy answer but obviously not :-(

[Andrew-R]

Sorry, but I'm not sure I follow what you're trying to do.

Is there any chance you could post a workbook with some example data in, please?

[SillySox]

ok - have tried - hopefully this example posts - first time I have tried to do this.

thanking you in advance for your help.

[SillySox]

ok - have tried - hopefully this example posts - first time I have tried to do this.

thanking you in advance for your help.

[Andrew-R]

Thanks for the workbook, I think I'm with you.

Give this a try:

Please Login or Register  to view this content.

[SillySox]

thankyou very much! this seems to have done the trick. I have now posted another thread relating to the same spreadsheet that I know you may find extremely easy but has 5 supposed 'advanced' excel users totally stumped. Good luck :-)