Use other workbook to act as a 'stop-list'

[D_N_L]

Hi all,

Sorry - not a very good title.

I have a macro where I have hardcoded which rows are deleted based on the below code. However this list may need to be updated quite frequently. Is there anyway - so that I can have others update it themselves – I can have a link to an external worksheet, where say, whatever is in column A of that worksheet would act as a stoplist.

So for example if on this 'stop-list' I have the words 'please', 'delete' and 'me' in cells A1, A2 and A3 then these rows would be deleted. I think I would need up to about 50 words (i.e. A1:A50).

Is this workable?

On Error Resume Next
With Range("A1:A" & ActiveSheet.UsedRange.Rows.count)
    .Replace "*please*", "True", MatchCase:=False
    .Replace "*delete*", "True", MatchCase:=False
    .Replace "*me*", "True", MatchCase:=False
    .Columns(1).SpecialCells(2, 4).EntireRow.Delete
End With

[natefarm]

Would this external workbook be already open?

[natefarm]

Assuming it's already open, this should work for you. In the other workbook, select the range of cells to compare against, and name it "DelList". Code could be added to automatically open and close it if needed.

Sub DeleteRows()
Dim Wkbk As String, ColNum As Long, RwCnt As Long

    Application.ScreenUpdating = False
    
    Wkbk = "Other Workbook.xlsx"
    ColNum = ActiveSheet.UsedRange.Columns.Count + 1
    RwCnt = ActiveSheet.UsedRange.Rows.Count

    Cells(1, ColNum).Formula = "=If(ISNA(Vlookup(A1," & Wkbk & "!DelList,1,False)),"""",True)"
    Cells(1, ColNum).AutoFill Range(Cells(1, ColNum), Cells(RwCnt, ColNum))
    
    On Error Resume Next
    With Columns(ColNum)
        .Copy
        .PasteSpecial (xlPasteValues)
        .SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        .Delete
    End With
    Range("A1").Select
    Application.ScreenUpdating = True
    MsgBox RwCnt - ActiveSheet.UsedRange.Rows.Count & " rows deleted."
End Sub

[D_N_L]

Hi natefarm,

Sorry for the late reply. No the workbook would not be open but I guess I could use a Workbook Open event to open it and then hide it? I shall have a play with your code. Many thanks.

[D_N_L]

Hi nate,

Hmm I couldn't get it to work. I did as you said - named the range in other workbook 'DelList' and saved the workbook. I then reopened it and ran my code from another file (which contained some criteria for deletion) but upon running the code a dialog box opened up asking me to select a file to 'update values'.

But I already had Other Workbook.xlsx open?

After I cancelled it the code then deleted everything in Col A?

[D_N_L]

Hi nate,

Got it working. I just needed to change this:

 Cells(1, ColNum).Formula = "=If(ISNA(Vlookup(A1," & Wkbk & "!DelList,1,False)),"""",True)"

To this:

    Cells(1, ColNum).Formula = "=If(ISNA(Vlookup(A1," & "'" & Wkbk & "'" & "!DelList,1,False)),"""",True)"

[natefarm]

Very good! If you need some ideas on how to optionally open the other workbook, this is one way:

Sub DeleteRows()
Dim Pth as String, Wkbk As String, ColNum As Long, RwCnt As Long

    Application.ScreenUpdating = False
    
    Wkbk = "Other Workbook.xlsx"
    Pth = "C:\yourpath\"

    On Error GoTo OpenWorkbook
    Workbooks(Wkbk).Activate
Continue:
    On Error GoTo 0

    ColNum = ActiveSheet.UsedRange.Columns.Count + 1
    RwCnt = ActiveSheet.UsedRange.Rows.Count

    Cells(1, ColNum).Formula = "=If(ISNA(Vlookup(A1," & "'" & Wkbk & "'" & "!DelList,1,False)),"""",True)"
    Cells(1, ColNum).AutoFill Range(Cells(1, ColNum), Cells(RwCnt, ColNum))
    
    On Error Resume Next
    With Columns(ColNum)
        .Copy
        .PasteSpecial (xlPasteValues)
        .SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        .Delete
    End With
    Range("A1").Select
    Application.ScreenUpdating = True
    MsgBox RwCnt - ActiveSheet.UsedRange.Rows.Count & " rows deleted."

OpenWorkbook:
    On Error GoTo FileNotFound
    Workbooks.Open Pth & Wkbk
    GoTo Continue

FileNotFound:
    MsgBox Err.Description
 End Sub