Find Value, delete that row and then the 4 next rows

[harleyskater]

This is what I have of course Rows(rng.Row + #).Delete is not real code, its just a place holder to show what I need. If anyone knows the code to tell vba to delete the following 4 rows please assist : )
I also have another excel vba question, I will post in another thread. Thank you!

    Dim rng As Range
    Dim what As String
    what = "Canada"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
            Rows(rng.Row + 1).Delete
            Rows(rng.Row + 2).Delete
            Rows(rng.Row + 3).Delete
            Rows(rng.Row + 4).Delete
        End If
    Loop
End Sub

 

[chergh]

Something like this:

Sub bleh()

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

i = ws.Cells.Find("Canada", LookIn:=xlValues).Row

ws.Rows(i & ":" & i + 4).Delete


End Sub

 

[ajetrumpet]

or perhaps, this:

    Dim rng As Range
    [color=red]Dim x as integer[/color]
    Dim what As String
    what = "Canada"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            [color=red]for x = 0 to 4
                rng.rows(x).entirerow.delete
            next x[/color]
        End If
    Loop
End Sub

Keep in mind that I'm not an expert at this.

 

[chergh]

or perhaps, this:

    Dim rng As Range
    [color=red]Dim x as integer[/color]
    Dim what As String
    what = "Canada"
    Do
        Set rng = ActiveSheet.UsedRange.Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            [color=red]for x = 0 to 4
                rng.rows(x).entirerow.delete
            next x[/color]
        End If
    Loop
End Sub

Keep in mind that I'm not an expert at this.

That wouldn't work as you are telling it to delete rows 0 to 4, you need to use the row property of the range returned by the find method.

 

[ajetrumpet]

oh yeah sorry. Just a thought... I'm obviously not that great here....

 

[harleyskater]

So first it finds a row with canada, deletes that entire row and then the 4 following rows, so 5 rows total. Is there a way to loop the code you gave?

Thank you guys for the help I am still working with the code : )
you guys gave me Thank you : ) : )

Is there a way to loop your code Chergh, like Ajetrumpet did with his?

That wouldn't work as you are telling it to delete rows 0 to 4, you need to use the row property of the range returned by the find method.

 

[chergh]

Sub bleh()

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

do while cbool(ws.Cells.Find("Canada", LookIn:=xlValues)) = true

i = ws.Cells.Find("Canada", LookIn:=xlValues).Row

ws.Rows(i & ":" & i + 4).Delete

loop

End Sub

 

[harleyskater]

Type mismatch error

Sub bleh()

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

[COLOR="Red"]do while cbool(ws.Cells.Find("Canada", LookIn:=xlValues)) = true [/COLOR][COLOR="Lime"]'error type mismatch[/COLOR]

i = ws.Cells.Find("Canada", LookIn:=xlValues).Row

ws.Rows(i & ":" & i + 4).Delete

loop

End Sub

 

[chergh]

I really should test my code before posting it. I'm not at a comp with excel installed at the moment but try this:

do while cbool(not ws.Cells.Find("Canada", LookIn:=xlValues) is nothing) = true

 

[harleyskater]

haha its running. ! it might take awhile! thats going to save me a bunch of work! gracias!!!!!!!!!!!!!!!!!!!!!!!!! CHERGH