Delete row base on other sheet

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
67
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.
 
Also would it be possible to clean the list in excwos before deleteing the rows in open vendor jobs?
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Actually I just found out that the parameters I was instructed to use will not work.
Can you please trim the macro for me. I do not need to clean the EXCWOS sheet.
So I only need the first half of the macro.
Thanks.
 
Upvote 0
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
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Add these lines of code:
Code:
 Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
below:
Code:
 Next x
Please note that this will over-write any existing copy without any warning.
 
Last edited:
Upvote 0
I tried that but it did not work. the active workbook returns the personal.xls. How can I specift that workbook?
 
Upvote 0
Which workbook contains the macro?
 
Upvote 0
Try using the full workbook name like this:
Code:
Workbooks("[COLOR="#FF0000"]workbookName.xlsx[/COLOR]").Save
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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