delete rows in source workbook if they exist in another workbook

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
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 )
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
ok, i can get a list of whats in the Date column using:

Code:
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,872
Office Version
365
Platform
Windows
How about
Code:
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
Delete part so far:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,872
Office Version
365
Platform
Windows
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
Code:
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
 
  • Like
Reactions: ajm

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
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
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,872
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,748
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,971
Messages
5,447,613
Members
405,458
Latest member
newbie111

This Week's Hot Topics

Top