Make this code column specific?

[Macdave_19]

Hi folks,

here is my code which looks up a piece of data from one sheet in another and clears it if it is present. (special thanks to RoyUK for the code)

Sub Rectangle18_Click()

    Dim i, j As Long
    
    For i = 4 To Sheets("Teams Used").Range("A" & Rows.Count).End(xlUp).Row
        For j = Sheets("Teams Left").Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
            If Sheets("Teams Used").Range("A" & i).Value = Sheets("Teams Left").Range("A" & j).Value Then
               Sheets("Teams left").Rows(j).ClearContents
            End If
        Next j
    Next i

End Sub

However i have lots of different colums in the sheet anmd i need it to only delete from a set colum in this case column "A" only.

please help folks

thanks for reading my thread and cheers for any help

[royUK]

Dave that's not my code and it looks specific to Column A. Is this what you want

Sub Rectangle18_Click()

    Dim i, j As Long
    
    For i = 4 To Sheets("Teams Used").Range("A" & Rows.Count).End(xlUp).Row
        For j = Sheets("Teams Left").Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
            If Sheets("Teams Used").Range("A" & i).Value = Sheets("Teams Left").Range("A" & j).Value Then
'clear only cell in A
               Sheets("Teams left").Cells("A" & j).ClearContents
            End If
        Next j
    Next i

End Sub

[Macdave_19]

Roy Roy Roy Roy,

what would i do without you? probabbly be jobless mate.

Top work as ever Buddy

[Macdave_19]

Hi Roy

'clear only cell in A
               Sheets("Teams left").Cells("A" & j).ClearContents

for some reason the code does not work and this line is highlighted when i try and Debug the code.

PLease help (Hands in the prayer position)

[antoka05]

Try to change so your code:

Sheets("Teams left").Range("A" & j).ClearContents

or

Sheets("Teams left").Cells(j, "A").ClearContents

Regards,
Antonio

[Macdave_19]

Antonio Your a top man,

i been messing with that code for ages too

Thanks very much!!