Comparing cells

[niklasbp]

Hey you all

i have a code in vba to compare cells, but it is super slow, and i'm sure that there is a better way to do it

what i have is 5 columns with random phone numbers, the problem is that sometimes the same number occurs twice in the same row

Therefore i have created a macro to clear the cell, if the number is already once in a row

my code looks like this:

--


Sub ARun()
Application.ScreenUpdating = False


Call FK
'Call FN
'Call FQ
'Call FT
'Call FW


'Call KN
'Call KQ
'Call KT
'Call KW


'Call NQ
'Call NT
'Call NW


'Call QT
'Call WQ


'Call TW




End Sub



Function FK()


Dim startRow As Integer
startRow = 1


Dim row As Integer
row = startRow


Dim bRow As Integer




Do While (Worksheets("Ark1_Levering").Range("F" & row).Value <> "")


Dim aVal As String
Dim bVal As String


aVal = Worksheets("Ark1_Levering").Range("F" & row).Value
bVal = Worksheets("Ark1_Levering").Range("K" & row).Value






bRow = startRow


Do While (Worksheets("Ark1_Levering").Range("F" & bRow).Value <> "")




If (aVal = bVal) Then



Worksheets("Ark1_Levering").Range("K" & row).Clear

Exit Do


End If


bRow = bRow + 1


Loop


row = row + 1
Loop


End Function

--

then i have a function for every two cells i want to compare, which is just a replica of the function FK you see above

i was thinking that a possible solution was to make a list instaed, where every row is a new list, where if the same number is twice it will clear one of them, but i dont know how to do it

i have +10.000 rows btw

best regards

/the vba newb

[Kenneth Hobs]

If you would make a short example of say 5 rows and obfuscate the data and mark the duplicates with say a red fill, it will be easier to help you.

When pasting code, paste between code tags. To insert them, click the # icon on the forum message toolbar or (code)MsgBox "Hi"(/code) where you replace ()'s with []'s.

[SamT]

If I understand, you have Phone numbers in Columns F, K, Q, T, and W.
There are no phone #s in any other columns.
Assumes never two duplicates in one row.

I don't know how fast this is, but it worked for me . The only data was in those columns, all else was blank.


Option Explicit

Sub ClearDupesInRows()

Dim Rw As Long 'Don't use Key Words As Variables (row is not a good variable name)
Dim LastRow As Long 'Last used Row in Column "F"
Dim Cols As Variant
Dim Dupe As Range

Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 6).End(xlUp).Row
Cols = Array(6, 11, 14, 17, 20, 23) 'Ph # Columns, by number

For Rw = 1 To LastRow
With Rows(Rw)
For C = 0 To 5

Set Dupe = .Find(What:=.Cells(Cols(C)), SearchDirection:=xlPrevious)
If Not Dupe Is Nothing Then
If Not Dupe.Address = .Cells(Cols(C)).Address _
Then Dupe.ClearContents
End If

Next C
End With
Next Rw

Application.ScreenUpdating = True
End Sub