delete rows in source workbook if they exist in another workbook

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
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 )
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top