Search code examples
excelvbasearchdelete-rowexcel-2016

Excel macro to remove rows containing variable text

This is my first time posting.

I'm trying to find a way in Excel 2016 to clean up a folder list so that I only have the parent folders.

I have a spreadsheet where column A is a list of folders, including their subfolders. Like this: [fyi - there are other columns in each row, but they are not relevant to this example]

\\server\share\root\subfolder1\
\\server\share\root\subfolder1\sub-subfolderA\
\\server\share\root\subfolder1\sub-subfolderB\
\\server\share\root\subfolder1\sub-subfolderC\
\\server\share\root\subfolder2\
\\server\share\root\subfolder2\other-subfolderA\
\\server\share\root\subfolder2\other-subfolderB\
\\server22\share\root\subfolder3\ham_sandwich\
\\server22\share\root\subfolder3\ham_sandwich\yet-another-subfolderA\
\\server22\share\root\subfolder3\and-another-subfolderA\
\\server22\share\root\subfolder3\and-another-subfolderB\

There are approximately 2500 rows with folders of varying length, and my ultimate goal is to end up with only the top level folders of each "set". For example:

\\server\share\root\subfolder1\
\\server\share\root\subfolder2\
\\server22\share\root\subfolder3\ham_sandwich\
\\server22\share\root\subfolder3\and-another-subfolderA\
\\server22\share\root\subfolder3\and-another-subfolderB\

The logic I have for this is as follows (and please correct me if I am overlooking anything):

See if the string in A1 is contained within the string in A2.
  If A2 contains the string, delete row 2.
  If it doesn't, move down to compare A2 with A3. [since we know A1 is now the only cell containing that top folder]
Rinse-and-repeat until the last populated row is reached.

My issue is figuring out the code for this. I'm seeing all sorts of code snippets online regarding searching for specified text, but nothing that uses a variable. I was originally toying with the formula combination of IsNumber and Search, but it requires fixed text to search, where this would be changing as the macro progresses.

Can someone point me in the right direction for this?

Solution

  • Assuming that the top level folder is always listed before the sub folders:

    k = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = k - 1 To 1 Step -1
    For j = k To i + 1 Step -1
        If InStr(Range("A" & j), Range("A" & i)) > 0 Then
            Rows(j).Delete
            k = k - 1
        End If
    Next j
Next i