PDA

View Full Version : problem deleting rows based on a condition in another workbook



mehdoush
03-16-2009, 07:10 AM
hey all

i'm using two workbooks "database.xls" and "ref to delete.xls"

"ref to delete.xls" contains reference numbers to delete in "database.xls",
when macro finds a that a ref number from "ref to delete.xls" in "database.xls", the entire row in "database.xls" that contains a ref. number in "ref to delete.xls" should be deleted.

notice: ref. no column is "A", in both workbooks


thanks in advance
regards

mehdoush
03-16-2009, 07:14 AM
database.xls

Bob Phillips
03-16-2009, 07:18 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim cell As Range
Dim sh As Worksheet

Set sh = Workbooks("database.xls").Worksheets(1)
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

Set cell = Nothing
cell = sh.Columns(1).Find(.Cells(i, "A").Value)
If Not cell Is Nothing Then

sh.Rows(cell.Row).Delete
End If
Next i
End With
End Sub

Bob Phillips
03-16-2009, 07:20 AM
Just seen that this is cross-posted at MrExcel http://www.mrexcel.com/forum/showthread.php?p=1873173&posted=1#post1873173

Here endeth m y assistance.

mehdoush
03-16-2009, 07:23 AM
hey xld ;)

thanks a lot

regards

mehdoush
03-16-2009, 07:25 AM
http://www.excelforum.com/excel-programming/675956-problem-delecting-rows-based-on-a-condition-in-another-workbook.html

mehdoush
03-16-2009, 07:33 AM
hey ;)

in which file should insert the vba code? "database.xls" or "ref to delete.xls" ?

thanks