delete content of one sheet off another sheet

[eureka]

I have got 2 excel sheet. Both contain a table(lets call it table1 and
table2). Both the tables have the same kind of formatting.

table1 contains other entries + table2

I want to remove table2 entries off table1.

What is the best way of going about it?

[Nigel]

One way is to scan table 1 and check each row (and column) in table 2 for a
match. If any column fails to match move to next table 2 entry. If all
columns match rrow from table 1.
If your tables are very big then this process can take a while to run.


Sub Test()

Dim t1LR As Long, t2LR As Long, t1CR As Long, t2CR As Long
Dim tCol As Integer, tCC As Integer, xMatch As Boolean

Application.ScreenUpdating = False

' get extent of table 1 and 2 and number of columns
' assumes tables start in column 1
t1LR = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
t2LR = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
tCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

' scan tables
' assumes tables start in row 1
For t1CR = t1LR To 1 Step -1
For t2CR = 1 To t2LR
xMatch = True
For tCC = 1 To tCol
If Sheets(1).Cells(t1CR, tCC) <> Sheets(2).Cells(t2CR, tCC) Then
xMatch = False
Exit For
End If
Next tCC
If xMatch = True Then Sheets(1).Rows(t1CR).EntireRow.Delete
shift:=xlShiftUp
Next t2CR
Next t1CR
Application.ScreenUpdating = True

End Sub

--
Cheers
Nigel



"eureka" <eureka @discussions.microsoft.com> wrote in message
news:[email protected]...
>I have got 2 excel sheet. Both contain a table(lets call it table1 and
> table2). Both the tables have the same kind of formatting.
>
> table1 contains other entries + table2
>
> I want to remove table2 entries off table1.
>
> What is the best way of going about it?
>
>