Vba assistance for duplicate data

[Donai]

Hi i am new and need some assistance in removing duplicate data, i have data which gets downloaded from in house system, the data is read as text, i need a vba code which will remove duplicate entries in each row, so below is a sample

Excel Workbook
	A	B	C	D	E
1	000147899	0147899	0147899
2	00673123	0673123	0673123	067312	0673123
Manual_Securities_Aliases v

I need the code to do this

Excel Workbook
	A	B
1	000147899	0147899
2	00673123	0673123
Manual_Securities_Aliases v

 

[Legacy 68668]

try

Sub test()
Dim a() As String, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
With Range("a1").CurrentRegion
    ReDim a(1 To .Rows.Count, 1 To Columns.Count)
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If (.Cells(i, ii).Value <> "") * (Not dic.exists(.Cells(i, ii).Text))Then
                n = n + 1 : dic(.Cells(i, ii).Text) = Empty 
                a(i, n) = .Cells(i, ii).Text
            End If
        Next
        dic.removeall
    Next
    .Value = a
End With
End Sub

 

[Donai]

Hello Seiya, i did test and i got an error 424 Object required

If (.Cells(i, ii) <> "") * (Not dic.exists(r.Text)) Then

try

Sub test()
Dim a() As String, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
With Range("a1").CurrentRegion
    ReDim a(1 To .Rows.Count, 1 To Columns.Count)
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If (.Cells(i, ii) <> "") * (Not dic.exists(r.Text)) Then
                n = n + 1 : dic(r.Text) = Empty 
                a(i, n) = .Cells(i, ii).Text
            End If
        Next
        dic.removeall
    Next
    .Value = a
End With
End Sub

 

[Legacy 68668]

try this one

Sub test()
Dim a() As String, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
With Range("a1").CurrentRegion
    ReDim a(1 To .Rows.Count, 1 To Columns.Count)
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If (.Cells(i, ii).Value <> "") * (Not dic.exists(.Cells(i, ii).Text))Then
                n = n + 1 : dic(.Cells(i, ii).Text) = Empty 
                a(i, n) = .Cells(i, ii).Text
            End If
        Next
        dic.removeall : n = 0
    Next
    .Value = a
End With
End Sub

 

[Donai]

Great, this works as required, thankyou very much Seiya.

try this one

Sub test()
Dim a() As String, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
With Range("a1").CurrentRegion
    ReDim a(1 To .Rows.Count, 1 To Columns.Count)
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If (.Cells(i, ii).Value <> "") * (Not dic.exists(.Cells(i, ii).Text))Then
                n = n + 1 : dic(.Cells(i, ii).Text) = Empty 
                a(i, n) = .Cells(i, ii).Text
            End If
        Next
        dic.removeall : n = 0
    Next
    .Value = a
End With
End Sub