Delete rows between specific text

[Mark F]

Hi

I have to do job weekly on a sheet with random number of rows ie one week 300 next week 10000

In column B, the word "product" appear regularly perhaps 30 times

I want to select the first row with the word "product" in it, then select the subsequent rows until the word "product" appears again in column B, then delete the selected rows (including that second row with "product")

I need to then find the next row with "product" in column B and repeat the process

I need to repeat this for all rows in the sheet

I have tried to find a solution on the forum but without musch success

Thanks for any help

Mark

 

[Dylan]

Try this code.

line1:
Sub Test()
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
counter = 1
For I = 1 To lastRow
    target = Cells(I, 2).Value
    
    If target = "product" Then
        If counter = 1 Then
            firstRow = I
            counter = counter + 1
        ElseIf counter = 2 Then
            secondRow = I
            Range(Cells(firstRow, 1), Cells(secondRow, 1)).EntireRow.Delete
            GoTo line1
        End If
    End If

Next I
End Sub

 

[Mark F]

Hi Dylan

Thanks for your code

My VBA knowledge is not great. I have tried the code and got "variable not defined as an error." Tried to "Dim" but no joy

Also the code refers to goto line 1. Not sure what it does

Any ideas how to fix it

Thanks

Mark

 

[Mark F]

Not being impatient, just wondering if anyone has altrnative options

Thanks for any help

Mark

 

[Dylan]

Sorry, Black Death, misplace the line1:
It should be work now.
This code will
1. delete the row between the 1st "product" & 2nd "product" (include 1st & 2nd "product" row.)

2. delete the row between the 3rd "product" & 4th "product" (include 3rd & 4th"product" row.)

and so on...

Sub Test() 
line1: 
lastRow = Cells(Rows.Count, 2).End(xlUp).Row 
counter = 1 
For I = 1 To lastRow 
    target = Cells(I, 2).Value 
    
    If target = "product" Then 
        If counter = 1 Then 
            firstRow = I 
            counter = counter + 1 
        ElseIf counter = 2 Then 
            secondRow = I 
            Range(Cells(firstRow, 1), Cells(secondRow, 1)).EntireRow.Delete 
            GoTo line1 
        End If 
    End If 

Next I 
End Sub

 

[Mark F]

Hi Dylan

Thanks for your help.

Code now works - my "variable not defined" error was because I had "Option Explicit" at start.

Also worked round the fact that in my sheets I have "PRODUCT" and "Product"

Regards

Mark