Deleting Duplicate Columns

@Bill

When you enter a formula, you ordinarily finish it off by pressing the Enter key, or even by clicking a cell with the mouse.

For an array formula you finish it off differently: you press the key combination Shift-Control-Enter.

If you do not, Excel does not treat it as an array formula and you will get a wrong result or perhaps an error. (It's possible to get a correct result, but only if: a) by chance, or b) due to Excel's "relative" handling of some functions. Also, the "by chance" possibility is usually only for the first cell with the array formula. If you put it in 10 cells, the other nine are probably wrong. And the second possibility only works in the rows looked at (for instance, if you have ten cells in rows 1-10 that you look at, but your array formula is in rows 4-13, only rows 4-10 might have correct results while rows 11-13 will give you an error, no matter what). And often a function is arranged differently or has dfferent options ("arguments") as an array formula than when it is not, so you can't even have chance work for you.

Since many formulas can work as array formulas, or not, you may not get an error and wonder what you did wrong: it looks perfectly all right so long as the result is not obviously impossible if it worked right (like you expect a result over 200,000 but the result you get is 43) and you go merrily on thnking you're golden though you're nothing of the kind.

Lastly, given the comment, I would also point out that looking at an array formula, you will see it looks different as well. It will have a curly brace ("{") BEFORE the "=" and another one ("}") after everything else"

=B1 NOT being treated by Excel as an array formula
{=B1} IS being treated by Excel as an array formula

Bill,

If you are not familiar with array formulas, then I recommend you don't use them. They are easy to accidentally break, especially if your workbook is used by others who are also not familiar with array formulas.

Here's a regular formula that will work just as well:
= SUMPRODUCT( - - ( A1:A100 = B1:B100 )) = ROW( A100 ) - ROW( A1 ) + 1

Adjust the row numbers as needed. For example, if your data starts in row 5 and contains 512 values:
= SUMPRODUCT( - - ( A5:A516 = B5:B516 )) = ROW( A516 ) - ROW( A5 ) + 1

Of course, if your data starts in row 5, and it currently contains 512 values, and you add more from time-to-time, and there's nothing else below your data, then this will work too and you won't have to adjust it.
= SUMPRODUCT( - - ( A5:A1000 = B5:B1000 )) = ROW( A1000 ) - ROW( A5 ) + 1

Knowing the pedantic nature of some readers of these tips, keep watching for more elegant solutions.

What do you mean, “Remember that an array formula is entered by using Shift=Ctrl+Enter.”? I don’t have a clue what you are referring to. I certainly could use this tip, so I appreciate any clarification.

Elegant solution
Just a remark. The need of "On Error Resume Next" command is obvious, but as a best practice I think that it should be followed by "On Error GoTo 0"
as soon as possible and in this preseny case just before the "n = rngData.Columns.Count" line.
That will contribute to easily debug any programming or mismatch error that may occur in the remaning part of the macro.

The macro can be shorter by using the array formula mentioned in this tip instead of the AreEqualArr function.
This formula can be implemented in vba by usingf the Evaluate method:

If Evaluate("AND(" & RngData.Columns(i).Address _
& "=" & RngData.Columns(j).Address & ")") Then

replaces

arr1 = rngData.Columns(i)
arr2 = rngData.Columns(j)
If AreEqualArr(arr1, arr2) Then

so we don't need the AreEqualArr function anymore:

Sub DeleteDuplicateColumns()
Dim rngData As Range
Dim i As Integer, j As Integer

On Error Resume Next
Set rngData = ActiveSheet.UsedRange
If rngData Is Nothing Then Exit Sub

For i = rngData.Columns.Count To 2 Step -1
For j = i - 1 To 1 Step -1
If Application.CountA(rngData.Columns(i)) <> 0 And _
Application.CountA(rngData.Columns(j)) <> 0 Then
If Evaluate("AND(" & rngData.Columns(i).Address & _
"=" & rngData.Columns(j).Address & ")") Then
With rngData.Columns(j)
.Copy 'mark column to be deleted
If MsgBox("Delete marked column?", vbYesNo) _
= vbYes Then
rngData.Columns(j).Delete
Else 'remove mark
Application.CutCopyMode = False
End If
End With
End If
End If
Next j
Next i