Removing Items from a Column A based on Same Values in Column B, while simutanously..

[AriX3]

Hi, So I have 3 Columns in an Excel Spread Sheet
Column A contains a list of values
Column B contains a list of values that may or may not be present in Column A
Column C has equal number of values as column A and the values correspond to each other

Now I need to remove ALL values from Column A that are in Column B

Ex.
Column A has
A
B
C
D
E
F
G

Column B has
B
C

Column C has
1
2
3
4
5
6
7


the resultant is Column A
A
D
E
F
G

Column B
B
C

Column C
1
2
3
4
5
6
7


this is done through this macro:

Sub TryMe()
    Dim rRangeA As Range
    Dim rRangeB As Range
    Dim rCell As Range
     
    Set rRangeA = Range("A1", Range("A65536").End(xlUp))
    Set rRangeB = Range("B1", Range("B65536").End(xlUp))
     
    For Each rCell In rRangeA
        If WorksheetFunction.CountIf(rRangeB, rCell) > 0 Then
            rCell.Delete
        End If
    Next rCell
     
End Sub

Now what i want to do is have this macro simutanously delete values from Column C as well

This means Column C should be
1
4
5
6
7



Any help is appreciated, im new to this, using Excel 2003.

[tlafferty]

Please see forum rules about enclosing your code in tags so it displays properly. [ CODE ] your code here [ /CODE ]. Here's a tweak to your code:

Sub TryMe()
Dim rRangeA As Range
Dim rRangeB As Range
Dim rCell As Range

Set rRangeA = Range("A1", Range("A65536").End(xlUp))
Set rRangeB = Range("B1", Range("B65536").End(xlUp))

For Each rCell In rRangeA
     If WorksheetFunction.CountIf(rRangeB, rCell) > 0 Then
          rCell.Delete
          rCell.Offset(0,1).Delete
     End If
Next rCell

End Sub

[AriX3]

Please see forum rules about enclosing your code in tags so it displays properly. [ CODE ] your code here [ /CODE ]. Here's a tweak to your code:

Sub TryMe()
Dim rRangeA As Range
Dim rRangeB As Range
Dim rCell As Range

Set rRangeA = Range("A1", Range("A65536").End(xlUp))
Set rRangeB = Range("B1", Range("B65536").End(xlUp))

For Each rCell In rRangeA
     If WorksheetFunction.CountIf(rRangeB, rCell) > 0 Then
          rCell.Delete
          rCell.Offset(0,1).Delete
     End If
Next rCell

End Sub

thanks, ill edit my inital post.

i tried what you wrote, it gave me runtime error 424?

[tlafferty]

try switching these two lines:

          rCell.Offset(0,1).Delete
          rCell.Delete

My guess is that after deleting rCell, Excel loses track of how to perform the offset.

[AriX3]

i added

rCell.Offset(0,2).Delete
rCell.Delete

and it seems to work, however it is deleteing some entries in Column B (this isint an issue, but strange?). Now the entry in column C deletes at the same time with Column A, just confusing as to why some entries in Column B are deleted????

[AriX3]

Wait no, its actually MOVING some values from Column C to Column B. this is weird.

[AriX3]

I tested with this

Test 1:

Col A	Col B	Col C
A	A	1
B	E	2
C	G	3
D		4
E		5
F		6
G		7

Output
Col A	Col B	Col C
B	A	2
C	E	3
D	G	4
F		6

As expected, works.

Test 2:

Col A	Col B	Col C
A	A	1
B	B	2
C	C	3
D		4
E		5
F		6
G		7

Output:
Col A	Col B	Col C
B	A	2
D	B	4
E	C	5
F		6
G		7

Does not work. Entry B should be removed. WHY?

In my real data set, even weirder things are happened, entries in column C are being added to column B some how.....

[AriX3]

If anyone has another function that can do this, ill would really appreciate it!

[tlafferty]

It may be useful to add stops to your code at strategic points, then watch as the code executes. Below, I've added stops at some crucial points. Click the restore window button and size your visual basic editing window so that you can watch the code as it executes.

Sub TryMe()
Dim rRangeA As Range
Dim rRangeB As Range
Dim rCell As Range

Set rRangeA = Range("A1", Range("A65536").End(xlUp))
Set rRangeB = Range("B1", Range("B65536").End(xlUp))

For Each rCell In rRangeA
     If WorksheetFunction.CountIf(rRangeB, rCell) > 0 Then
          Stop
          rCell.Delete
          Stop
          rCell.Offset(0,1).Delete
     End If
Next rCell

End Sub

See if this helps isolate what's happening.

[AriX3]

nope didnt work.... highlights the stops in yellow...

[AriX3]

I changed the code, works fine now. used a do until loop. thanks for the help!