Turn Off Autocomplete in Specific Columns in Excel

How to turn off Autocomplete in specific columns in an Excel worksheet.

Watch the training video below:

Turn Off Autocomplete in Specific Columns in Excel

How can we turn off auto-complete in specific columns in an Excel worksheet. Auto-Complete in Excel is a useful feature but it can become a hindrance during data entry in some cases.

Let’s say we enter data like ‘Item1’ in a cell or range. Next we try to enter another item with the same alphabet ‘i’ in the cell below then we observe that the Autocomplete feature automatically displays ‘Item1’. If we are in a hurry we might just press enter to get the same entry. This may not be our intention!

Now how can we ensure that the Autocomplete feature is disabled in specific columns like column 1 (column A) and column 2 (column B). Before we write VBA the code to automatically disable the AutComplete feature, let us see where this feature is located. If we select ‘Options’ from the File menu and select Advanced from the new Excel Options window that pops up, we can observe that the item ‘Enable AutoComplete for Cell Values’ is checked as shown in the image below:

Enable AutoComplete for Cell Values
Enable AutoComplete for Cell Values

Unchecking this item will disable the auto-complete feature for all cells in the worksheet. Our aim is to disable the autocomplete feature only for specific cells or columns. In order to achieve our goal we will right-click on our worksheet name and select view code. In the new window called Microsoft Visual Basic for Applications Editor, we will select the drop-down arrow next to the object General and select worksheet. As soon as we do this two lines of code appear in our Editor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Between these lines we will write our single line of VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableAutoComplete = Intersect(Target, Union(Me.Columns(1), Me.Columns(2))) Is Nothing

End Sub

We can get more information about the Intersect method at this web link. An example of Intersect is shown below:

An Example of Intersect
An Example of Intersect

In VBA Union means joining two or more ranges together. We can find some interesting examples of Union at this link. Target refers to the cell(s) being changed. More details about the target cell are available at this hyperlink. ‘Is Nothing’ refers to the fact that the ranges don’t intersect.

Download a sample file:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Turn Off Autocomplete in Specific Columns in Excel”

  1. Hello Sir,
    Can you please help for the formula for below request

    Having value in cell A like ABC1200+12+123+11 and want to separate this as

    in column B as ABC1200
    in column C as ABC1212
    in column D as ABC1123
    in column E as ABC1211

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.