Deleting specific rows

问题:

I have to generate a Excel VBA macro for a chainage calculator, so I have to leave the first entry and last entry of a specific name and delete all the values in between. For example:

EXAMPLE DATA IMAGE

So I want to leave the first Rivor Minor253 and the last Rivor Minor253, but all the middle ones must be deleted. Now, I could do this manually but I have over 3,000 rows which will take me days to complete, and I could generate a macro which deletes duplicates but I don't know how to generate one which will only delete the middle ones.

Could anyone provide me with a macro that fits my description?

回答1:

Turn on Record Macro first, if inclined to do so. Assuming CHAINAGE is in ColumnA, in Row39 and copied down to suit please try:

=OR(COUNTIF(B$1:B39,B39)=1,COUNTIF(B$1:B39,B39)=COUNTIF(B:B,B39))  

Then filter that column to select and delete rows FALSE.

回答2:

As a macro, this is surprisingly simple.

Sub del_brick()
    Dim fr As Long, lr As Long

    With Worksheets("Sheet1")  '<~~ set this properly!
        lr = Application.Match("zzz", .Columns(2))
        Do While lr > 40  '<~~ if lr is above row, there is nothing to delete
            fr = Application.Match(.Cells(lr, 2).Value2, .Columns(2), 0)
            If CBool(lr - fr - 1) Then
                .Range(.Cells(fr + 1, 1), .Cells(lr - 1, 1)).EntireRow.Delete
            End If
            lr = fr - 1
        Loop
    End With
End Sub