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

**Andrew-R** · 02-12-2013, 03:55 AM

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

**SillySox** · 02-12-2013, 09:13 PM

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