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 instead, where every row is a new list, where if the same number is twice it will clear one of them, but i don't 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