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: