Making AutoComplete Work for an Entire Column

At least with Excel LTSC Standard 2021 (16.0.14334.20848 64-bit) on Windows, auto-completion does not require the cells in the range for auto-completion values to be filled contiguously.

They just need to be in the current column and part of a two-dimensional contiguous "vicinity range" of filled cells: Starting from the current cell where you enter the first character, Excel seems to check all 8 surrounding cells whether they are filled with anything. Even filled with ="" would mean filled. If one of the surrounding cells is filled, a one- respectively two-dimensional vicinity range is spun up from the current cell to that filled cell. Same with the other surrounding cells. When one of them is filled, the vicinity range gets extended to it. Looking at the 8 cells surrounding the current cell is the first iteration of the process of extending the vicinity range. It's maximum size is 3 x 3 cells with the current cell in the center. Excel then recursively extends the vicinity range in all directions as long as it finds surrounding cells of the already reached vicinity range, which are also filled with anything. Once no surrounding filled cell is found, the final extension of the vicinity range is found. In other words, the final vicinity range is the maximum range with filled cells in each row and column, surrounded by only empty cells or edges of the sheet whatever comes first. In VBA that vicinity range is called CurrentRegion (referenced by the Range property CurrentRegion). At the Excel interface, when clicking Ctrl-*, that vicinity range will be marked.

From that in general two-dimensional range Excel takes only the row numbers to determine the one-dimensional vertical range in the column of the current cell which - except for the current cell - holds the auto-completion values.

As an example think of a chess bord where only the black squares are filled. All values of the black fields above and below are considered for auto-completion. Even with many configurations that make much more sparing use of the black squares in the columns to the right and left, the result is the same.

Is there any way to suppress the algorithm that Excel uses detects a header row in the list? Reference: "Autocomplete may not work" h t t p s :
/ / d o c s . m i c r o s o f t . c o m /en-us/office/troubleshoot/excel/autocomplete-not-work - - this says that "In Microsoft Excel, the Auto-Complete feature may not fill in the remaining characters if the algorithm that Excel uses detects a header row in the list."

When you right-click on a cell, and select "Pick from drop-down list" from the dialog, then a dropdown box appears with the contents of the previous cells in that column. It would be very convenient if this was more streamlined. This appears to follow the same rules as the author has described for AutoComplete. I am assuming these functions are directly related under the covers. Is these any way that this AutoCompleteDropDownBox can be evoked in VBA (such as with a doubleclick or sendkeys) so that you wouldn't have to go through all the usual steps for this to appear?

I would like your Newsletter, but I have Excel V 2003 "Home & Student" which I hardly use now, and V 2016. I have just followed your tip in my 2016 regarding auto complete whole column and IT WORKED!!! I entered a space in a cell, then dragged it down a number of rows. The spreadsheets are for my records only and I will rarely print them.

I note you support the older versions, but I need help on my 2016 version - such as linking worksheets and cells etc. I managed it years ago in V 2003 but have forgotten how I did it!!

Will your weekly newsletter help me with v 2016?

Jane

When the cell at the right and/or the left of an empty cell in a column has data Excel considers this cell not as an empty cell for AutoComplete. Consequently this empty cell becomes part of the contiguous range in the column.

So, in this tip's example, B7 and B25 being empty, AutoComplete will work if cell A7 or C7 and A25 or C25 have data.

Hello Frank
I think you can describe "it" as a limitation or a feature.
"It" works in one way in Excel and in another way in LibreOffice.
I expect if you ask a thousand people which way they prefer, you will get mixed answers. Some people may have something to do one day which favours the way "it" works in LibreOffice., and another day somethhung they do might work better if "it" works in the way it does in Excel.
Alan Elston

LibreOffice does not have this idiotic limitation.

There is a way to get the auto complete to work in a column where there are empty cells in that column, without putting anything in the empty cells. But it relies on some complicated inter dependencies / inter connection of adjoining filled cells in other columns
H t t p s : / / w w w .excelforum.com/the-water-cooler/1181732-small-trick-in-excel-2.html#post4636789

Alan Elston

I prefer using the single quote character '. It does not print and does not require any special formatting to suppress.