Delete rows between specific text

Mark F

Well-known Member
Joined
Jun 7, 2002
Messages
500
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
Try this code.

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

Well-known Member
Joined
Jun 7, 2002
Messages
500
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

Well-known Member
Joined
Jun 7, 2002
Messages
500
Not being impatient, just wondering if anyone has altrnative options :biggrin:

Thanks for any help

Mark
 

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
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...

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

Well-known Member
Joined
Jun 7, 2002
Messages
500
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,018
Messages
5,628,167
Members
416,297
Latest member
Kara Payne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top