I have the following code that I would like to change. I initially wrote this to work with a specific couple of files, and I'm finding that I need to have it be able to work for multiple files for both the dnc/ccl lists. I've never been good at code that allows you to select files from an open dialog. Can someone help me with this, either with sample code for selecting files, or by tweaking the below?
Code:Sub Cleanup() Dim sTmpEmail As String 'Email address we're checking Dim iDNClist As Integer 'Loop counter for Do Not Call List Dim iCustList As Integer 'Loop coutner for ColoradoCustomers spreadsheet Dim dnc As Worksheet 'Do Not Call worksheet Dim ccl As Worksheet 'Customer list worksheet Set dnc = Workbooks("DoNotMailBook.xlsm").Worksheets("DoNotMailSheet") Set ccl = Workbooks("Customer ListBook.xlsx").Worksheets("CustomerListSheet") Dim dncS As Range 'Do Not Call Cell A1 Dim cclS As Range 'Customer List cell A1 Set dncS = Workbooks("DoNotMailBook.xlsm").Worksheets("DoNotMailSheet").Range("A1") Set cclS = Workbooks("Customer ListBook.xlsx").Worksheets("CustomerListSheet").Range("A1") Dim dncC As Integer 'Do Not Call last row Dim cclC As Integer 'Customer List last row dncC = dnc.Range("A65536").End(xlUp).Row cclC = ccl.Range("A65536").End(xlUp).Row Debug.Print dncC Debug.Print cclC 'dnc.Range("A1").Font.Bold = False 'ccl.Range("A1").Font.Bold = False Application.ScreenUpdating = False For iDNClist = 1 To dncC sTmpEmail = dncS.Offset(iDNClist, 0).Value For iCustList = 1 To cclC If sTmpEmail = cclS.Offset(iCustList, 0).Value Then cclS.Offset(iCustList, 0).EntireRow.Delete cclC = cclC - 1 Else End If Next iCustList Next iDNClist Application.ScreenUpdating = True End Sub




Reply With Quote