Solved: Finding Duplicated Column

[antonc]

Hi Guys,

I have 30 columns of Data (10 rows) Please see attached.

I would like an easy way to check whether there are any columns that have been duplicated - ie. same sequence down across the row and then highlight or remove the duplicated column.

Is there any way to do this at all.

[Bob Phillips]

You have confused rows and columns in your description.

Tell us an example of duplication in that data?

[nst1107]

This should do what you want.
Option Base 1
Sub deleteDuplicates()
Dim ctrlArry(), chkArry() As String
Dim vrtLim As Long
Dim x, y, z As Integer
Dim flag As Boolean
With Sheet1
vrtLim = .UsedRange(.UsedRange.Count).Row
ReDim ctrlArry(vrtLim), chkArry(vrtLim)
x = 1
Do Until .Cells(1, x) = "" 'Loop through all ctrlArry()
For y = 1 To vrtLim 'Fill ctrlArry()
ctrlArry(y) = .Cells(y, x)
Next
z = x + 1
Do Until .Cells(1, z) = "" 'Loop through all chkArry()
For y = 1 To vrtLim 'Fill chkArry()
chkArry(y) = .Cells(y, z)
Next
flag = True
For y = 1 To vrtLim 'Compare arrays
If ctrlArry(y) <> chkArry(y) Then flag = False
Next
If flag = True Then
.Columns(z).Delete
Else
z = z + 1
End If
Loop
x = x + 1
Loop
End With
End Sub

Copy and paste into the declarations of a module and hit F5.

[antonc]

Thanks NST - that worked great...