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

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

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

Const sDATA_SHEET As String = "Sheet1"
Const sSEARCH_SHEET As String = "Sheet2"
Const sSEARCH_RANGE As String = "A:A"
Const sSTART_RANGE As String = "A9"

Dim rngLoop As Range
Dim rngFind As Range

Set rngLoop = Sheets(sDATA_SHEET).Range(sSTART_RANGE)

While rngLoop.Value <> ""
  If rngLoop.Offset(0, 1).Value <> "" Then
    Set rngFind = Sheets(sSEARCH_SHEET).Range(sSEARCH_RANGE).Find(rngLoop.Offset(0, 1).Value, Lookin:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    While Not rngFind Is Nothing
      rngFind.EntireRow.Delete
      Set rngFind = Sheets(sSEARCH_SHEET).Range(sSEARCH_RANGE).Find(rngLoop.Offset(0, 1).Value)
    Wend
  End If
  Set rngLoop = rngLoop.Offset(1)
Wend

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?

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

thanking you in advance for your help.

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

thanking you in advance for your help.

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

Give this a try:

Const sDATA_SHEET As String = "Sheet1"
Const sSEARCH_SHEET As String = "Sheet2"
Const sSEARCH_RANGE As String = "A:A"
Const sSTART_RANGE As String = "A9"

Dim rngLoop As Range
Dim rngFind As Range
Dim lColLoop As Long

Set rngLoop = Sheets(sDATA_SHEET).Range(sSTART_RANGE)

While rngLoop.Value <> ""
  lColLoop = 1
  While rngLoop.Offset(0, lColLoop).Value <> ""
    Set rngFind = Sheets(sSEARCH_SHEET).Range(sSEARCH_RANGE).Find(rngLoop.Offset(0, lColLoop).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    While Not rngFind Is Nothing
      rngFind.EntireRow.Delete
      Set rngFind = Sheets(sSEARCH_SHEET).Range(sSEARCH_RANGE).Find(rngLoop.Offset(0, lColLoop).Value)
    Wend
    lColLoop = lColLoop + 1
  Wend
  Set rngLoop = rngLoop.Offset(1)
Wend

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 :-)