Find and Delete data in a column
 

Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet A
with column A in Sheet B. For every match I want to delete the entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul

Find and Delete data in a column
 

Rahul

Maybe it's because you refer to Column B in Sheet A ? [Range("B" & i)]

Why not turn it round and go from sheet B and look for matching entries in
sheet A. You could use COUNTIF to check for a match. It's also better to
work from the last row up so that you don't end up jumping over rows.

Something like:

Sub DeleteDuplicates()
Dim LastRow As Long
Dim i As Long
Sheets("B").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("A").Range("A:A") , Range("A" & i))
0 Then
Range("A" & i).EntireRow.Delete
End If
Next 'i
End Sub

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet A
with column A in Sheet B. For every match I want to delete the entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul

Find and Delete data in a column
 

I thank Trevor for pointing out the error I made in my effort to
simplify the code for the newsgroup.

i = 2
'Start of loop

Sheets("A").Select
store = Range("A" & i) '---and not Range("B" & i) as in my
previous post
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
'some more code
'Finally block

Loop

The code still gives the error. Any help shall be appreciated.

Best Regards,
Rahul


"Trevor Shuttleworth" wrote in message ...
Rahul

Maybe it's because you refer to Column B in Sheet A ? [Range("B" & i)]

Why not turn it round and go from sheet B and look for matching entries in
sheet A. You could use COUNTIF to check for a match. It's also better to
work from the last row up so that you don't end up jumping over rows.

Something like:

Sub DeleteDuplicates()
Dim LastRow As Long
Dim i As Long
Sheets("B").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("A").Range("A:A") , Range("A" & i))
0 Then
Range("A" & i).EntireRow.Delete
End If
Next 'i
End Sub

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet A
with column A in Sheet B. For every match I want to delete the entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul

Find and Delete data in a column
 

Trevor,

Your code is pithy and effective. It works. Thanks a lot. Although I
did not find out what was wrong with the code I was using I had the
task on hand done with your code.

Best regards,
Rahul

(rahul_chatterjee) wrote in message . com...
I thank Trevor for pointing out the error I made in my effort to
simplify the code for the newsgroup.

i = 2
'Start of loop

Sheets("A").Select
store = Range("A" & i) '---and not Range("B" & i) as in my
previous post
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
'some more code
'Finally block

Loop

The code still gives the error. Any help shall be appreciated.

Best Regards,
Rahul


"Trevor Shuttleworth" wrote in message ...
Rahul

Maybe it's because you refer to Column B in Sheet A ? [Range("B" & i)]

Why not turn it round and go from sheet B and look for matching entries in
sheet A. You could use COUNTIF to check for a match. It's also better to
work from the last row up so that you don't end up jumping over rows.

Something like:

Sub DeleteDuplicates()
Dim LastRow As Long
Dim i As Long
Sheets("B").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("A").Range("A:A") , Range("A" & i))
0 Then
Range("A" & i).EntireRow.Delete
End If
Next 'i
End Sub

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet A
with column A in Sheet B. For every match I want to delete the entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul

Find and Delete data in a column
 

Rahul

glad to be able to help. Sometimes it's just easier to start afresh than
try to work out why something is broken ;o)

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Trevor,

Your code is pithy and effective. It works. Thanks a lot. Although I
did not find out what was wrong with the code I was using I had the
task on hand done with your code.

Best regards,
Rahul

(rahul_chatterjee) wrote in message
. com...
I thank Trevor for pointing out the error I made in my effort to
simplify the code for the newsgroup.

i = 2
'Start of loop

Sheets("A").Select
store = Range("A" & i) '---and not Range("B" & i) as in my
previous post
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
'some more code
'Finally block

Loop

The code still gives the error. Any help shall be appreciated.

Best Regards,
Rahul


"Trevor Shuttleworth" wrote in message
...
Rahul

Maybe it's because you refer to Column B in Sheet A ? [Range("B" & i)]

Why not turn it round and go from sheet B and look for matching entries
in
sheet A. You could use COUNTIF to check for a match. It's also better
to
work from the last row up so that you don't end up jumping over rows.

Something like:

Sub DeleteDuplicates()
Dim LastRow As Long
Dim i As Long
Sheets("B").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("A").Range("A:A") , Range("A" &
i))
0 Then
Range("A" & i).EntireRow.Delete
End If
Next 'i
End Sub

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet
A
with column A in Sheet B. For every match I want to delete the
entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul