Delete row base on other sheet

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
For each cell in workbook1 colum "A" i would like to compare that against a list stored in workbook2 and if it is found delete the entire row from workbook 1.
Additionally for the #'s in workbook2 if they do not exist in workbook1 delete that cell from workbook2. Also workbook2 would notrmally be closed when the macro from workbook1 is a activated. Can this be done? I have not the experience yet to do so alone. All support would be appreciated.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,200
What is the full path to the folder containing workbook2? What is the full name of workbook2 including the extension (xlsx, xlsm)? What are the names of the sheets containing the lists in each workbook?
 

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
Workbook1 page name is "Open Vendors Jobs", Workbook2's "C:\Users\edward.selkov\Working\ExcludedWo's.xlsm" - worksheet name is "EXCWOS"

Thank you.
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,200
Copy/paste this macro in a standard module in Workbook1 and run it from there.
Code:
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, x As Long
    Set desWS = ThisWorkbook.Sheets("Open Vendors Jobs")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    Set srcWB = Workbooks.Open("C:\Users\edward.selkov\Working\ExcludedWo's.xlsm")
    Set srcWS = Sheets("EXCWOS")
    For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For x = LastRow To 2 Step -1
        If RngList.Exists(desWS.Cells(x, 1).Value) Then
            desWS.Rows(x).EntireRow.Delete
        End If
    Next x
    RngList.RemoveAll
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each Rng In desw.Range("A2", desw.Range("A" & desw.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For x = LastRow To 2 Step -1
        If Not RngList.Exists(srcWS.Cells(x, 1).Value) Then
            srcWS.Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub
Make sure you have backup copies of your files just in case something goes wrong.
 
Last edited:

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
Thanks for the quick reply. I did as you asked and on first run I get a subscript out of range on this line
Code:
Set desWS = ThisWorkbook.Sheets("Open Vendor Jobs")
 

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
I changed thisworkbook to active workbook and the first part of the code seems to work. Put now I get and undefined error here.

Code:
    For Each Rng In desw.Range("A2", desw.Range("A" & desw.Rows.Count).End(xlUp))
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,200
This line of code should work properly:
Code:
 Set desWS = ThisWorkbook.Sheets("Open Vendor Jobs")
Sometimes using "ActiveWorkbook" can give unexpected results. Double check the sheet name to make sure it is an exact match. Look for beginning or trailing spaces. If you still have problems, I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,200
There were a few typo's on my part. Try:
Code:
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, x As Long
    Set desWS = ThisWorkbook.Sheets("Open Vendor Jobs")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    Set srcWB = Workbooks.Open("C:\Users\edward.selkov\Working\ExcludedWo's.xlsm")
    Set srcWS = Sheets("EXCWOS")
    For Each Rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For x = LastRow To 2 Step -1
        If RngList.Exists(desWS.Cells(x, 1).Value) Then
            desWS.Rows(x).EntireRow.Delete
        End If
    Next x
    RngList.RemoveAll
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each Rng In desWS.Range("A2", desWS.Range("A" & desWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For x = LastRow To 2 Step -1
        If Not RngList.Exists(srcWS.Cells(x, 1).Value) Then
            srcWS.Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub
 

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
Thank you. But this line still gives a subscript out of range error. If I change it to active workbooks it seems to run fine.

Code:
    Set desWS = ThisWorkbook.Sheets("Open Vendor Jobs")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,867
Messages
5,489,369
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top