delete rows in source workbook if they exist in another workbook

[ajm]

Hi all, I am working on a little procedure to import data from one file into my Source workbook. Currently works fine although possibly long winded. A new requirement was added this morning to remove rows in the source workbook where they exist in the new workbook prior to importing them. The data relates to appointments. So, if the new data contains appointments for the first week of June (3-8), and my existing data includes appointments up to the 5th June, prior to importing the new appointments for the coming week, i need to delete any appointments in the Source workbook that are made for the 3rd, 4th, & 5th June.

I think this could be done by creating a list of dates from both workbooks using advanced filter, then deleting the records from the source workbook where the dates match??? Is there an easier, or more efficient method?

(ps i have posted this question at the bottom of another thread containing code so far for this job. https://www.mrexcel.com/forum/excel-questions/1097827-why-copy-paste-so-tricky-vba.html )

 

[ajm]

ok, i can get a list of whats in the Date column using:

Sub GetUniques()
' hiker95, 07/26/2012
' http://www.mrexcel.com/forum/showthread.php?649576-Extract-unique-values-from-one-column-using-VBA
Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 2).End(xlUp).Row
c = Range("b2:b" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
Worksheets("Novice").Range("B2").Resize(d.Count) = Application.Transpose(d.keys)
End Sub

However, this messes up the dates (Australia uses dd/mm/yyyy) swapping the months and days around.

 

[ajm]

I don't understand what its doing either.

I take it that the object "d" is created to hold all the different dates found in column b of the active sheet.

its then copied to the Novice page. since I am working with dates, it must be reformatting them to US dates "mm/dd/yyyy". can the format of the output be specified perhaps?

 

[Fluff]

How about

Sub ajm()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         .Item(CLng(Cl.Value)) = Empty
      Next Cl
      Sheets("Temp").Range("A2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

 

[ajm]

Interesting.

Fluff, your code above returns the column on the left and hiker95's code returns the column on the left:

30/05/2019 30/05/2019
3/06/2019 6/03/2019
4/06/2019 6/04/2019
6/06/2019 6/06/2019
10/06/2019 6/10/2019
11/06/2019 6/11/2019
29/05/2019 29/05/2019
31/05/2019 31/05/2019
5/06/2019 6/05/2019
7/06/2019 6/07/2019


apologies in advance for the appearance of the, something has happened to my htmlmaker addin.

Now on to the delete part of the macro. to refresh, i need to delete any dates in my source workbook that appear in the list that is created from the imported file.

 

[ajm]

Delete part so far:

Sub Delete_with_Autofilter_More_Criteria()
    Dim rng As Range
    Dim cell As Range
    Dim CriteriaRng As Range
    Dim calcmode As Long
    Dim LastRow As Integer

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


' move list into source wb
    With Sheets("Dates")
            .Move After:=MainWS
    End With
    
'Criteria values held in array
    Set CriteriaRng = Sourcewb.Sheets("Dates").Range("A2", Sourcewb.Sheets("Dates").Cells(Rows.Count, "A").End(xlUp))

'fill array with list
    ReDim adays(0 To 0) As String
    i = 1
    'Loop through the cells in the Criteria range
    For Each cell In CriteriaRng

    ReDim Preserve adays(0 To UBound(adays) + 1) As String
        adays(i) = Str(cell.Value)
        i = i + 1
    Next cell

        With MainWS

            On Error Resume Next
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
            On Error GoTo 0
 
                LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                'Apply the filter
                .Range("b1:b" & LastRow).AutoFilter Field:=1, Criteria1:=adays, Operator:=xlFilterValues

the autofilter to the MainWS is not displaying the values from the array. Can anyone throw me a bone?

 

[Fluff]

If you just want to use the dates to filer a sheet, there's no need to right them to an intermediate sheet, you can use something like

Sub ajm()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         .Item(CDate(Cl.Value)) = Empty
      Next Cl
      Sheets("Main").Range("A1").AutoFilter 2, .Keys, xlFilterValues
   End With
End Sub

 

[ajm]

If you just want to use the dates to filer a sheet, there's no need to right them to an intermediate sheet, you can use something like

Sub ajm()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         .Item(CDate(Cl.Value)) = Empty
      Next Cl
      Sheets("Main").Range("A1").AutoFilter 2, .Keys, xlFilterValues
   End With
End Sub

whaaaaaaaT!? you're a witch! awesome. Thanks for that Fluff. so simple and works straight up.

 

[Fluff]

You're welcome & thanks for the feedback

 

[ajm]

hey Fluff, thought i would use the structure of what you showed me above to this time to filter for the names that do not appear in the range. so, previously, if a date was common to both spreadsheets that date was passed to the filter. ultimately, it was then deleted. Now, I want to delete all entries where the value in a particular cell is not in my list.