-4

I have a spreadsheet with ~3500 rows and 5 columns.

Column A contains URLs. Some of the URLs are fully qualified domains, some include multiple sub-directories wit the same FQD.

I want to delete all of overlapping URLs except the fully qualified domain (www.example.com)

For example I might have the following:

www.example.com

www.example.com/sub-directory-a

www.example.com/sub-directory-b

www.example.com/sub-directory-a/sub-c/sub-d

I need to delete every row except www.example.com

Ken Prince
  • 1,437
  • 1
  • 20
  • 26
  • 1
    use a combination of FIND() and MID() then on the DATA tab Remove Duplicates –  Aug 27 '14 at 14:23
  • 1
    or use Autofilter to check for `/` and then delete the filtered data – Siddharth Rout Aug 27 '14 at 14:24
  • 1
    [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) will get you started ;) – Siddharth Rout Aug 27 '14 at 14:25
  • Can someone clarify why I might have received 3 down votes for this type of question so I can correct the mistakes in the future? – Ken Prince Aug 28 '14 at 13:38

1 Answers1

2

Deletes all rows with "/"

Sub RowKiller()

    Dim F As Range, rKill As Range
    Set F = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    Set rKill = Nothing
    For Each r In F
        v = r.Text
        If v Like "*/*" Then
            If rKill Is Nothing Then
                Set rKill = r
            Else
                Set rKill = Union(r, rKill)
            End If
        End If
    Next r

    If Not rKill Is Nothing Then
        rKill.EntireRow.Delete
    End If

End Sub
mrbungle
  • 1,921
  • 1
  • 16
  • 27
  • What you suggested is already covered [HERE](http://stackoverflow.com/questions/21413167/how-to-delete-column-from-range-if-cell-contains-specific-value-in-vba-excel) The autofilter is faster than this method :) – Siddharth Rout Aug 27 '14 at 16:35
  • @SiddharthRout thanks, I'll look more into that method :) – mrbungle Aug 27 '14 at 16:50