Macro Error Please help

[bkbri]

Hello,

When I add this Macro script to excel work sheet I get a Syntax erro
could someone please help me identify whats wrong with this script.

Sub Sample()
Set ColAValues = ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
ColBLastRow = ActiveSheet.Range("B65536").End(xlUp).Row

For i = ColBLastRow To 1 Step -1
CurrentValue = ActiveSheet.Cells(i, 2).Value
For Each Cell In ColAValues
If Cell.Value = CurrentValue Then
ActiveSheet.Cells(i, 2).Delete shift:=xlUp
End If
Next Cell
Next i
End Sub

Its supposed to check columns B and delete any value it finds tha
match in Column A

Heres an example...

___A___B__
1.| N | H |
2.| B | I |
3.| C | J |
4.| D | K |
5.| E | L |
6.| F | M |
7.| G | N |<--- N found deleting duplicate in cell A1


Thanks,
Bria

 

[Greg Wilson]

It works for me if you correct for the word wrap. The
following line should all be in one row:

Set ColAValues = ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)

If the above code was not corrected for word wrap it
should have been highlighted with red font. Was this not
the case with you ???

Although there seems nothing wrong with the code, you can
remvove all ActiveSheet references since the active sheet
is the default when a range is not qualified. Suggested
is as follows. Correct for the word wrap.

Sub Sample()
Set ColAValues = Range("A1:A" & Range("A65536").End
(xlUp).Row)
ColBLastRow = Range("B65536").End(xlUp).Row

For i = ColBLastRow To 1 Step -1
CurrentValue = Cells(i, 2).Value
For Each Cell In ColAValues
If Cell.Value = CurrentValue Then
Cells(i, 2).Delete shift:=xlUp
End If
Next Cell
Next i
End Sub

Regards,
Greg

 

[Cecilkumara Fernando]

bkbri,
change
Set ColAValues=ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
to
Set ColAValues=ActiveSheet.Range("A1:A" & _
ActiveSheet.Range("A65536").End(xlUp).Row)
to run the macro
from your worksheet
Tools>Macro>(select the macro you want run and click run)
Cecil

 

[bkbri]

Thanks for the help