Delete all rows under a group

[Klartigue]

See the attached sheet. In this spreadsheet, i need to find the word "Closed Accounts" in column B, and delete not only the row that has the word "closed accounts" in column B but also delete all the rows that have text below it. Any ideas on how to do this?

[CodeNinja]

This might work for you...

Sub deleteAll()
Dim rng As Range
Dim rngDelete As Range


Set rng = Sheet1.Range("B:B")
Set rngDelete = rng.Find(What:="Closed Accounts")

Sheet1.Range("B" & rngDelete.Row & ":B" & Sheet1.Range("B65536").End(xlUp).Row).EntireRow.delete

End Sub

[Klartigue]

There is an error that says object variable or With block variable not set

[Opv]

There is an error that says object variable or With block variable not set
Presuming there are never going to be any data rows below the row containing the cell with the value, "Closed Accounts," that you want to retain, the following might work for you (of course you may need to change your sheet name as applicable):


Sub testing()

Dim rng As Range, fRow, lRow As Integer

With Sheets("Sheet1")

lRow = .UsedRange.Rows.Count
fRow = .Range("B:B").Find(what:="Closed Accounts").Row

Set rng = .Range("B" & fRow, .Range("B" & lRow))

rng.EntireRow.Delete

End With

End Sub

[Klartigue]

Thats works great, thank you!

[Opv]

Thats works great, thank you!

Note that I edited the code in my previous post. It accomplishes the same thing but gets rid of a couple of lines of unnecessary code. Anyway, glad it worked for you.

[dazwm]

You may find this code useful. If you have more than just one word to look for and delete the entire row, you highlight the column in your case 'B' then run this code. It asks you to put what words to look for in the input box then deletes the entire row.

Not quite what you are looking for in this case but may come in useful in the future.



Sub DeleteRowInInputBox ()

Dim s As String, iCol As Long, LR As Long, i As Long
Dim R As Range, F As Boolean, j As Long
iCol = ActiveCell.Column
s = "£"
Do While s <> ""
s = LCase(InputBox("Enter word to look for"))
If s = "" Then Exit Sub
LR = Cells(Rows.Count, iCol).End(xlUp).Row
For i = 1 To LR
If LCase(Cells(i, iCol).Value) Like "*" & s & "*" Then
If R Is Nothing Then
Set R = Cells(i, iCol)
j = j + 1
Else
Set R = Union(R, Cells(i, iCol))
j = j + 1
End If
F = True
ElseIf Cells(i, iCol).Value <> "" Then
F = False
Else
If F Then
If R Is Nothing Then
Set R = Cells(i, iCol)
j = j + 1
Else
Set R = Union(R, Cells(i, iCol))
j = j + 1
End If
End If
End If
Next i
If Not R Is Nothing Then
R.EntireRow.Delete
MsgBox j & " rows deleted", vbInformation
Set R = Nothing
End If
F = False
Loop
End Sub

[snb]

Wouldn't this suffice ?

Sub snb()
Columns(2).Find("Closed Accounts", , xlValues, xlWhole).CurrentRegion.EntireRow.Delete
End Sub

or

Sub snb()
Columns(2).Find("Closed Accounts", , xlValues, xlWhole).resize(cells(rows.count,2).end(xlup).row).EntireRow.Delete
End Sub

[dazwm]

Wouldn't this suffice ?



Yes it would. The code I provided is if there are more than one criteria that is needed in one sheet. Rather than going into the code and changing it which would be hard for a beginner, they would just enter the words one after another in the input box.

[snb]

I referred to http://vbaexpress.com/forum/showpost.php?p=269199&postcount=1 (http://vbaexpress.com/forum/showpost.php?p=269199&postcount=1)

[Opv]

Wouldn't this suffice ?

Sub snb()
Columns(2).Find("Closed Accounts", , xlValues, xlWhole).CurrentRegion.EntireRow.Delete
End Sub
or

Sub snb()
Columns(2).Find("Closed Accounts", , xlValues, xlWhole).resize(cells(rows.count,2).end(xlup).row).EntireRow.Delete
End Sub

I like the brevity. Your suggestion is my first exposure to the CurrentRegion method.