delete rows between two values in a column

[shadyshawn]

So I have a set of data like below, but with a much longer list.

<table style="width: 121px; height: 468px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Summary Data
xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Total 1204</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Summary Data</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">xx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Total 1500</td> </tr> </tbody></table>
What I need is to delete rows between the cell "Summary Data" and the cell "Total ###". Note that the number after each "Total" is different on my list. I got a code but it deletes all the rows between first "Summary Data" and the last "Total ###" cell.Can anyone help me on this? Appreciated!

This is the code I have:

Dim F1 As Range, F2 As Range
With Sheets("Sheet3")
    Set F1 = .Columns("A").Find(what:="Summary Data", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    Set F2 = .Columns("A").Find(what:="Total", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    If Not F1 Is Nothing And Not F2 Is Nothing Then .Rows(F1.Row & ":" & F2.Row).Delete
End With

 

[venkat1926]

after settubg F1 and F2 why not just the coded

range(f1.offset(1,0),f2.offset(-1,0).entirerow.delete

see whether it works


in f2 find "total" by xlpart is ok
but in F1 find "summary data" why not xlwhole instead of xlpart . is there any other words beyond or before "summary data"

 

[Domenic]

Maybe...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] FoundCells [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell1 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell2 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] SearchRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FirstAddress [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=darkblue]Const[/color] SrchTerm1 [color=darkblue]As[/color] [color=darkblue]String[/color] = "Summary Data"
    [color=darkblue]Const[/color] SrchTerm2 [color=darkblue]As[/color] [color=darkblue]String[/color] = "Total"
    
    [color=darkblue]Set[/color] SearchRng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    
    [color=darkblue]With[/color] SearchRng
        [color=darkblue]Set[/color] FoundCell1 = .Find(what:=SrchTerm1, after:=.Rows(.Rows.Count), LookIn:=xlValues, _
            lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell1 [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            FirstAddress = FoundCell1.Address
            [color=darkblue]Do[/color]
                [color=darkblue]Set[/color] FoundCell2 = .Find(what:=SrchTerm2, after:=FoundCell1, LookIn:=xlValues, lookat:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                [color=darkblue]If[/color] FoundCell2 [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
                [color=darkblue]If[/color] FoundCell2.Row < FoundCell1.Row [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
                [color=darkblue]If[/color] FoundCells [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    [color=darkblue]Set[/color] FoundCells = Range(FoundCell1, FoundCell2)
                [color=darkblue]Else[/color]
                    [color=darkblue]Set[/color] FoundCells = Union(FoundCells, Range(FoundCell1, FoundCell2))
                [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Set[/color] FoundCell1 = .Find(what:=SrchTerm1, after:=FoundCell1, LookIn:=xlValues, _
                    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            [color=darkblue]Loop[/color] [color=darkblue]While[/color] FoundCell1.Address <> FirstAddress
       [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCells [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        FoundCells.EntireRow.Delete
    [color=darkblue]Else[/color]
        MsgBox "No rows were found...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]