0

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

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
yabxi
  • 3
  • 3
  • Is the data already sorted so that same rows go one after each other, or there is no sorting and macro need to go through all of the rows and pick up same ones? – GSazheniuk Nov 19 '15 at 04:08
  • Yes the data is already sorted by Crossing Name. All the Rivor Minor253 are under eachother, and same goes with Rivor Minor248, Rivor Minor250, etc. @GSazheniuk – yabxi Nov 19 '15 at 04:10

2 Answers2

0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Sorry, do you mean select Row39 till Row50? And how do I filter it? – yabxi Nov 19 '15 at 04:31
  • I've selected all of columnA, and went into DATA > Sort & Filter, clicked on the little down arrow, unchecked everything but FALSE wasn't there. The numbers from columnA were there. – yabxi Nov 19 '15 at 04:46
  • Okay I did it, and it deleted all the middle rows from Row39 to Row50. So do I have to do this for all other names like Rivor Minor248, etc? – yabxi Nov 19 '15 at 04:51
  • What I meant was how do write =OR(COUNTIF(B$1:B39,B39)=1,COUNTIF(B$1:B39,B39)=COUNTIF(B:B,B39)) in a way for it to do it automatically for every different Rivor Minor? Or do I have to do every Rivor Minor manually? http://postimg.org/image/hn3rvi5vl/ – yabxi Nov 19 '15 at 05:02
  • Thank you, after a few trials and errors I finally understood what you meant, haha. @pnuts – yabxi Nov 19 '15 at 07:02
0

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
  • What do you mean if lr is above row, there is nothing to delete? I got the rest of the macro code, thanks. – yabxi Nov 19 '15 at 06:56
  • The sample image you provided shows the column header data in row 38. The first row of Chainage entries in row 39. For anything to delete the last row must be row 41 or higher because if it was row 40 there would be no 'middle' rows to delete. –  Nov 19 '15 at 07:13