Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting Duplicate Columns.

# Deleting Duplicate Columns

Written by Allen Wyatt (last updated May 9, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003

Dror has a worksheet that contains quite a bit of data. It is possible that the data in one column will be exactly the same as the data in another column, so he wonders if there is an easy way to delete any duplicate columns within the worksheet.

The first step, of course, is to figure out if two columns are identical or not. This can be determined rather easily with an array formula such as the following:

```=AND(A1:A100=B1:B100)
```

(Remember that an array formula is entered by using Shift+Ctrl+Enter.) The formula compares all the values in the first 100 rows of columns A and B. If they are all the same, then the formula returns TRUE. If any of the cells don't match, then the formula returns FALSE. If the result is TRUE you could then delete one of the columns because they are the same.

If you want something that is a bit more automatic, meaning that the duplicate column is deleted, then you'll need to use a macro. The following steps through all the columns in the worksheet and, starting with the right-most column, compares all the columns. If any are the same—regardless of their order in the worksheet—then the macro asks if you want the duplicate column deleted.

```Sub DeleteDuplicateColumns()
Dim rngData As Range
Dim arr1, arr2
Dim i As Integer, j As Integer, n As Integer

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

n = rngData.Columns.Count

For i = n To 2 Step -1
For j = i - 1 To 1 Step -1
If WorksheetFunction.CountA(rngData.Columns(i)) <> 0 And _
WorksheetFunction.CountA(rngData.Columns(j)) <> 0 Then
arr1 = rngData.Columns(i)
arr2 = rngData.Columns(j)
If AreEqualArr(arr1, arr2) Then
With rngData.Columns(j)
'mark column to be deleted
.Copy
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

End Sub
```
```Function AreEqualArr(arr1, arr2) As Boolean
Dim i As Long, n As Long
AreEqualArr = False
For n = LBound(arr1) To UBound(arr1)
If arr1(n, 1) <> arr2(n, 1) Then
Exit Function
End If
Next n
AreEqualArr = True
End Function
```

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7164) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Duplicate Columns.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Wrapping Text around a Graphic in a Text Box

Word allows you to wrap text around a graphic or around a text box, but it won't allow you to wrap text in a text box ...

Discover More

Creating One-time Labels

Need to create a set of labels for a specific purpose? The easiest way is to let Word create a set of blank labels and ...

Discover More

Selecting Printing of Color Pictures

Do you want to control whether color pictures in your document are printed or not? It's not quite as easy as it may ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

Limiting Number of Characters in a Cell

Need to limit the number of characters that can be entered into a cell? One easy way to do it is through the use of Data ...

Discover More

Typing Check Marks into Excel

Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the ...

Discover More

Ensuring Standard Units During Data Entry

Need to make sure that information entered in a worksheet is always in a given unit of measurement? It's not as easy of a ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 1?

2021-03-26 21:50:52

dt

Do you have a version of this that will do the same for rows? No headers and use the active selection?

Very Respectfully,

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.