Removing rows between earlier removed rows in Excel

Question

I am to write VBA code that would remove certain rows (with certain words in a column). I have one that is below. I have problem with removing also all rows that are between two earlier removed rows (which are subtotals)(*and as condition that this is only one row between them, if more not to delete them)

Could you please advise is there a way to overcome this problem? Please let me know if this question is vague (as I am newcomer)

Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String

Set SrchRng = ActiveSheet.Range("F1", ActiveSheet.Range("F10000").End(xlUp))
SrchStr = InputBox("Please Enter A Search String")
Do
    Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

End Sub

Answer 1

You've got the column of interest (F in this case) and you're also prompting the user to specify the string for deletion, which sounds like the perfect recipe for crafty use of the Range.Autofilter method. (Here's an MSDN link: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.autofilter(VS.80).aspx)

Supposing the block of data you are operating on goes from A:F, you could write:

Option Explicit
Sub DeleteRowsWithAutoFilter()

Dim DataRng As Range, DeleteMe As Range
Dim SrchStr As String

'set references up-front
Set DataRng = ActiveSheet.Range("A1", ActiveSheet.Range("F10000").End(xlUp))
SrchStr = InputBox("Please Enter A Search String")

'turn off alerts for delete action
Application.DisplayAlerts = False
DataRng.AutoFilter Field:=6, Criteria1:=SrchStr '<~ apply autofilter
With DataRng
    Set DeleteMe = .Offset(1, 0) '<~ set the delete range
    DeleteMe.Delete
End With
ActiveSheet.AutoFilterMode = False
Application.DisplayAlerts = True

End Sub