Trying to search through list in workbook x find values in workbooks y and z and then delete row.

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Hi, So this was supposed to be fairly straight forward.
I have two lists in two sheets in one excel file. (Sheet 1 and Sheet 2 in Workbook A)
Then I have two other excel files with 1 sheet in each of them. Sheet 1 Work Book B. Sheet 1 Work Book C.

So Sheet 1 in Workbook "Main" has a list of values in column A. I need a vba script that can look through column A. and find the matching value in Column A of Sheet 1 workbook "Rad" and IF Found then delete the row in Sheet 1 WorkBook "Rad".

Then I need it to look Column A in Sheet 2 of work Book "Main" and find the matching value in column A Sheet 1 Work Book "Vogue". And if it finds the value then delete the row in Workbook "Vogue".

Here is the code so far.

VBA Code:
Dim U1 As Worksheet, U2 As Workbook, U3 As Worksheet, i As Long, fn As Range, L1 As Worksheet, L2 As Workbook, L3 As Worksheet, l As Long, ln As Range

Set U1 = Workbooks("Main.xlsm").Sheets("Sheet 1")
Set U2 = Workbooks.Open("M:\Thisplace\Rad.csv")
Set U3 = U2.Sheets(1)


    For i = U3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        Set fn = U1.Columns("A").Find(U3.Cells(i, 1).Value)
        If Not fn Is Nothing Then Rows(i).Delete
  

  U2.Close Savechanges:=True

Next

  Set L1 = Workbooks("Main.xlsm").Sheets("Sheet 2")
  Set L2 = Workbooks.Open("M:\Thisplace\Vogue.csv")
  Set L3 = L2.Sheets(1)

For l = L3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        Set ln = L1.Columns("A").Find(L3.Cells(i, 1).Value)
        If Not ln Is Nothing Then Rows(i).Delete
  

  L2.Close Savechanges:=True

  Next

ThisWorkbook.Close Savechanges:=True


End Sub


I don't know why but I get "Cells' of Object'_Worksheet' failed - error message. Can someone please tell me what I am doing wrong?
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Try closing the workbooks after the loop not during it.
 
Solution

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Try closing the workbooks after the loop not during it.
Hi that seemed to work but for whatever reason it doesn't seem to complete the loop. In other words, it doesn't look through the entire list. It only finds a few of the listed items

Do you see anything wrong with the way I have written the script for the loop itself?

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
If Not fn Is Nothing Then U3.Rows(i).Delete
 

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Try
VBA Code:
If Not fn Is Nothing Then U3.Rows(i).Delete
Thank you.

The issue was that
1., like you said, I should have closed outside the loop.
2. I should have written it like this:

For l = L3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Set ln = L1.Columns("A").Find(L3.Cells(l, 1).Value)
If Not ln Is Nothing Then Rows(l).Delete

i was using i for both files as opposed to l.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top