Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: delete rows in source workbook if they exist in another workbook

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default delete rows in source workbook if they exist in another workbook

    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-...ricky-vba.html )
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    Quote Originally Posted by Fluff View Post
    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.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete rows in source workbook if they exist in another workbook

    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.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •