Search all open workbook and if find the match data , clean the cell on Column A

[parscon]

I have about 10 workbooks and each workbook have about 5 sheets and I have another workbook Main that have only one sheet and have a data only in one column A .

Now i need a VBA code that search data on Workbook main on sheet 1 column A with all other sheets on all workbook with range A to P if does not find any data on all workbooks and sheets , delete that cell on column A .

Note : after complete search on all workbook and sheets will delete that data on cell column A on workbook main .


Hope you understand what i need .

[westconn1]

you can try like

Set mn = Workbooks("main.xls")
lastrow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & lastrow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "Main.xls" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Nextuntested, will check all open workbooks, except main, assumes that all workbooks to search are already open, but easy to change if not

[parscon]

Thank you very much bit when run the VBA code show me the error : Sub Or Function not defined

and also i have a question : is this VBA code first check all open workbooks after that delete ?

And also i need to clean the cell not delete the row on workbook main . only clean data on the cell in column A Sheet1

Thank you again .

Note: i use Excel 2013

[westconn1]

And also i need to clean the cell not delete the row on workbook mainif not delrow is nothing the delrow.clear


Sub Or Function not definedon which line?


is this VBA code first check all open workbooks after that delete ?all sheets in all open workbooks, then delete if not found


Note: i use Excel 2013change workbook names to suit

[parscon]

on which line?



Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required

Complie Error
Sub Or Function not defined

and yellow mark the first line Sub Datacheck()
and blue mark the latest a (a



Sub Datacheck()
Set mn = Workbooks("main.xls")
LastRow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & LastRow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "main.xls" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Next
End Sub

[westconn1]

typo
Set fnd = ws.Range("A:P").Find(arr(i, 1))

[parscon]

Dear westconn1 ,Thank you but another error :

on line :


LastRow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row


And the error is :

Run-time error '438': Object doesn't support this property or method

[westconn1]

try
LastRow = mn.Sheets(1).Cells(mn.sheets(1).Rows.Count, 1).End(xlUp).Row

[parscon]

Sorry , It is work i forget add .xlsx for main in your code.

Thank you very much for your big help



Sub Datacheck()
Set mn = Workbooks("main")
LastRow = mn.Sheets(1).Cells(mn.Sheets(1).Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & LastRow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "main" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(arr(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Next
End Sub

[parscon]

Just there is a problem:

In column A on sheet 1 workbook main , i have a number 2323 and also in other workbook i have data like 22202323 and when run VBA code it will not delete the 2323 on column A on sheet 1 workbook main .i think it must be check the exact item .

I tested this but same result , it will not check exact .


Set fnd = ws.Range("A:P").Find(arr(i, xlWhole))


Please help me on this also

[westconn1]

Set fnd = ws.Range("A:P").Find(arr(i, 1),,, xlwhole)
this was in some other thread of yours, it is the lookat parameter of the find method

[parscon]

Dear westconn1

Thank you very much , and it is work very fine .