Small trick in excel

I can see the values OK, but have no idea how you did that...

It is easy. A hint is in my signature.

Duhhh.... Not that easy...

Hi tim,
This is a nice trick . Thanks for sharing.
I could not understand your instructions or the point about the hint in your signature ???

I actually did this once before in answering a Thread. I forgot how to do it , and in trying to figure out what you were doing it reminded me, so thanks

This was the situation I had:
_1) The OP had a lot of horrible formatting including lots of merged and coloured cells. ( Which incidentally are a pet hate of mine ).
_2) I needed to merge ( bring in data ) from the data worksheet, Sheet2 and insert it in the appropriate column in sheet1 based on matching column headings.

The problem was that the Headings were written differently in different rows in the main worksheet, Sheet1.

So for example:
This was Sheet 2
Using Excel 2007 32 bit

Row\Col	A	B	C
1	Heading1	Heading2	Heading3
2	1	2	3

Worksheet: Sheet2

This was Sheet 1
Using Excel 2007 32 bit

Row\Col	A	B	C	D	E
1			Secondheading	Not
2		Head1		a heading
3				column , or
4				a love song	3rdTitle
5
6				just a
7				lot of
8				Poxy
9				formatting

Worksheet: Sheet1

_......

Now to get a simple VBA merge code to work is easy if you have in Sheet1 a row with the headings spelt exactly the same as in Sheet2

So this is what I did

I change row 5 in Sheet 1 to include some help Headings, so it looks like this ( As it is Easter, I thought I would hide an Easter Egg also:
Using Excel 2007 32 bit

Row\Col	A	B	C	D	E
5		Heading1	Heading2	Egg	Heading3

Worksheet: Sheet1

In some arbitrary spare range of 5 horizontal cells anywhere, I merge those cells and colour them yellow. ( There is no significance to the colour – you can even leave the background colour white ). But important: In that cell ( or in cell G7 before you merge), I have any word but coloured yellow, so you do not see it. If you do not colour the background , then that word has to have colour white ) ( I changed the colour slightly so that you can just about see it )
In fact you may not need to add any word – it depends a bit on exactly what you are doing. You need to experiment a bit.. If you want to do it a bit like tim did then you put a number in A5 before you paste in the format of the merged cells. That word will then be shown)
Using Excel 2007 32 bit

Row\Col	G	H	I	J	K
8	Any Word Yous like.

Worksheet: Sheet1

I copy that range
I then paste in that range across my help Heading range (A5: to E5), but only select for Format to be pasted in
PasteInFormatOnly.JPG http://imgur.com/ZNj0Xy9

This is how my Sheet1 now looks. ( So it just looks like the OP originally had and wanted - He or she does not see my help Headings )
Using Excel 2007 32 bit

Row\Col	A	B	C	D	E
1			Secondheading	Not
2		Head1		a heading
3				column , or
4				a love song	3rdTitle
5	Any Word Yous like.
6				just a
7				lot of
8				Poxy
9				formatting

Worksheet: Sheet1

But those help Headings are still there and VBA will see them, just as the Sum formula from tim saw those hidden numbers

I expect that explanation is exactly what you were saying, tim. With hindsight, after I knew the answer, I could understand what you were saying.

Great trick , tim, Thanks for sharing
Happy Easter

P.S.
Another trick is just to colour the help headings white ( or whatever colour the background colour of the cell is to hide the help headings)

Sorry, I do not understand at all
I have no idea what you are saying.
I see no difference in Sheet1 or Sheet2
All of the autofill I have tried works excactly the same in both worksheets

Originally Posted by tim201110

skip a row in sheet 1 and 2 and begin to input any string from A1:A3

Hi tim,
Ahh, silly me, I had forgotten what autofill was !! Or rather I was assuming that Excel Autofill was referring to being able to “drag down” a value, ( fill by using the fill handle http://www.excelfunctions.net/Excel-Autofill.html __ or various copy and paste techniques http://www.eileenslounge.com/viewtop...=24852#p192980 http://www.eileenslounge.com/viewtop...=24852#p192987 http://www.eileenslounge.com/viewtopic.php?f=27&t=25068 )


I think we are referring here to Automatically repeat values that already exist in a column

The normal characteristics is that if I begin typing any word immediately below a row of already fully filled in ( no empty rows ) section, then Excel helps me and suggests one of the above words such as this: _...
autofill.JPG http://imgur.com/MNOQYRL
_..- for that screenshot, I had just typed _ d _ into cell A4, but Excel offered me a full word.

If I had attempted that in cell A5 , Excel would have not tried to help me with a suggestion.

The above is the auto fill that I know.
It appears only to work for rows immediately above until no spaces are encountered. ( Actually it will work for all “connected” rows** ).

For example here in cell A5_....
Using Excel 2007 32 bit

Row\Col	A	B
1	abc
2	y
3	dfe
4	ghi
5	y
6	y

Worksheet: 1 (2)
¬_..... I would get offered _ dfe _ if I typed _ d _ into cell A5 _ . Similarly , if I typed _ g _ into cell A5 , Excel would offer me _ ghi _ ( in cell A6 Excel would not help me )
In cell A2 if I began typing the first letter of any of those character strings, then Excel will offer me the full character string beginning with that letter. ( So in fact this Automatically repeating of values that already exist in a column works for all connected rows**. )

In your Sheet2 , the limitations caused by empty rows have been removed

I cannot see how you have achieved this. ??? I can achieve this by filling all cells in the column with any character and giving them a colour of white , should I not wish to see them.

If I could do this whilst leaving the cells blank, that could be quite useful.

Care to explain how you have done it ?

Alan

Hi tim,

Originally Posted by tim201110

input ="" in B1:B23, then coied that range and pasted values in it..

Ahh, I see the cells contain strings of zero length and so are not Empty.
Useful trick, Thanks
( I am not too familiar with Array Formulas and merged cells, but I guess that could interest a formula person ).

By the way,

Hey tim, do you know that we have a Tips and Tutorials Forum
Tips and Tutorials.jpg http://imgur.com/SqrCRIl
https://www.excelforum.com/tips-and-tutorials/

You can post there also.

But Posting in Water Cooler and some other Off Topic Forums is also good for fun things
Off Topic Forum.JPG http://imgur.com/qOvV2vA
Off Topic Forum2.JPG http://imgur.com/eiB8pqt

Have a good Easter Sunday

Alan

Not quite so, Alan.
Cells are empty. Excel just rememberes that somewhen there was something in them.
It is a good example of VBA property Usedrange, it is not accurate. I never use it.

Hi tim,
OK.
Things associated with UsedRange I find always a bit tricky and usually I get it wrong.. Lol... ( UsedRange makes me puke ! )
Empty cells and the such usually confuse me as well ..

But I think you and me are saying the same thing.

UsedRange sees the “” ( string of zero length ).


I was thinking along __ these lines...
Consider __ some code lines ( https://www.excelforum.com/developme...ml#post4631463 )

Make a new fresh Worksheet
Using Excel 2007 32 bit

Row\Col	A	B	C
1
2
3
4
5

Worksheet: NewSheet

Put this code in a code module in the Workbook where that worksheet is..
(__ Code for you is here:
Sub TimTrickExcelSuggestcellvalues()
https://www.excelforum.com/developme...ml#post4631463


Run that Code in Debug ( F8 ) mode

Line 50 does this:

Row\Col	A
2
3	SausagesGermanTasteGood
4

Worksheet: NewSheet

Once you pass line 70, you will be able to see in the Watch window the following:
CellContentEmpty.JPG http://imgur.com/XYzinZM
It shows you that the contents of cell A4 are Empty ( There is nothing in it )
Line 90 confirms this fact. Line 90 tells you that the UsedRange last row is 3

Try now to type _ S _ in cell A5. Excel will not help you. Excel will not suggest "SausagesGermanTasteGood" ( Excel in it’s used range of experience has not touched on cell A5.. :Lol )

( Line 110 is necessary to clear anything you may have typed in cell A5 )

Line 140 is the equivalent of you typing _ =”” _ in cell A4

After passing line 150 you will see in the Watch window _ ”” _ This is a value of a zero length string. This is the value held in the Range object for that cell .

( After passing line 170 you will see in the Watch window _ ”=””” _ This is the formula held in the Range object for that cell . )

Cell A4 is not empty. Line 190 confirms this. Line 190 tells you that your UsedRange has extended to have its last row = 4

Line 200 is very interesting . I personally believe that .Value is not a property as they suggest. I believe it is a Method, or at least acts like one ( https://fastexcel.wordpress.com/2017.../#comment-4143 )
.Value appears to be intelligent in line 200
In line 200 the cell A4 is not given the value of a zero length string “” as we might have expected. It is in fact , Emptyied.
After line 220 you will see that the call content is Empty. Line 210 confirms this, as line 210 will tell you that the last row of the UsedRange is 3.
If, after line 200, you stop the code and try to type _ S _ in cell A5, Excel will not help you. Excel will not suggest "SausagesGermanTasteGood" ( Excel in it’s used range of experience has forgotten it touched on cell A5 Its usedRange memory was cleared ( It was actually cleared at line 210 by use of its Properties – but that is a different issue ) .. )

In line 260 we again write into cell A4 _ =”” _
But this time we use the Range copy Method in line 270 followed by the Range PasteSpecial Method in line 280 to paste in the value of the cell.
After line 290 we see in the immediate window that the cell is not empty. It contains a zero length string _ “” CellContentZerolenghString.JPG http://imgur.com/1ubM3VF

Line 310 confirms this. Line 310 tells you that the UsedRange has its last row in row 4.

Try now to type _ S _ in cell A5. Excel will help you. Excel will suggest "SausagesGermanTasteGood" ( Excel in it’s used range of experience has touched on cell A5.. :Lol )

_...._______________

In end effect we are saying the same thing...
_ If a cell has a “” in it , ( a string of zero length ), then it is used . So it is counted as in the UsedRange.
_ If you clear that cell it is then Empty. So it is not counted as in the UsedRange


UsedRange sees the “” ( string of zero length ).


Alan

Originally Posted by tim201110

One can explain everything, but miracle has been so wonderful.
http://listenonrepeat.com/watch/?v=2DaY8-Mui0I#Queen_-_The_Miracle_(Official_Video)

Fredy Mercury said to me at Knebworth Park, Stevanage ( Aug 1987 ) ( well actually he was speaking to a few thousand people including me ) : “ .... I was in to all that before Live Aid and all that Shit ..... “
I would have liked to have been able to ask him questions, Freddy was my Hero. ..... , as it weres
http://listenonrepeat.com/watch/?v=2DaY8-Mui0I#Queen_-_The_Miracle_(Official_Video)
Who wants to live for ever ? .. ( http://listenonrepeat.com/watch/?v=WvA4hrpCSew#Queen_-_Who_Wants_to_Live_Forever_(1986) ).. Well I wouldn’t mind actually ... if we are all friendly to each other life can be so fuking wonderful ..... and there is so bloody much to learn ... “...we can learn forever....” ... in everything .. and VBA is good example of this .....

_.___________________________________________________________
So, back Off Topic Liitle Excel Trick ..
Being able to extend the rows in which Excel’s helps with “Automatically repeat values that already exist in a column” could be useful to me , and possibly to others, so after finally sussing it out I am sharing my findings..
In Post #15 of the Thread we are in now, https://www.excelforum.com/the-water...ml#post4631529 ,.. I think we came to the conclusion ( after going through the code I gave, https://www.excelforum.com/developme...ml#post4631463 ) that in tim’s example, Excel’s helping with “Automatically repeat values that already exist in a column” was working even when there are seemingly Empty cells between where you type in and the data from which Excel’s helping with “Automatically repeat values that already exist in a column” helps you , was in fact, as those cells in between were not Empty!! Those cells were not empty because they had a string of zero length in them. That was my conclusion, but I appear to have been wrong. Similarly my later findings here suggest that it is not particularly to do with UsedRange . – Rather it is to do with CurrentRegion.

Going back to the File from Tim uploaded in Post # 7 , Autofill.xlsx ( https://www.excelforum.com/the-water...ml#post4631249 )

Originally Posted by tim201110

...input ="" in B1:B23, then copied that range and pasted values in it. Voila.. example of UsedRange ...

This is what both Worksheets “1” and “2” from Tim, looks like (after applying the ExcelForum Water Cooler object Property of messing up the text for Off Topic Easter light joviality):

Please Login or Register  to view this content.

But, I looked again , I noticed. In fact, In Worksheet “2” the cells are , indeed Empty - They do not in fact, contain a zero length string as I had wrongly assumed. Cells A4:A23 in Worksheet “2” are Empty But little Excel trick do work!!!
CellContentInTinSheet2AREEmpty.JPG http://imgur.com/ycKWd2p

Oh dear... :-(( I did not look far enough!! )
So I try again: - New code
https://www.excelforum.com/developme...ml#post4632633
https://www.excelforum.com/developme...ml#post4632636
( Code is in two parts to fit in due to post size limitations but it is all one code )

Please Login or Register  to view this content.

90-110 We can apply the Value ( so called ) Property , argument (RangeValueDataType:=xlRangeValueDefault) to return the value of a cell, ( the cell contents )
Or
140-160 we apply the .Value ( so called ) Property , argument (RangeValueDataType:=xlRangeValueDefault) to return a field of Variant type elements , ( an array of Member elements of Variant type ), to the Range object of A1:A25 to see what that returns as the cell contents. ( In VBA we may assign this Field directly to a dynamic array declared (Dim ed) as Variant type Member Elements. The array will then be sized appropriately to , and then accept, those elements from the Field into its member elements This array can be examined in the Watch Window whilst running the code in debug, F8 , mode )

What do we see !!! 160
Cells A4:A23 in Worksheet “2” are Empty
CellContentInTinSheet2AREEmpty.JPG http://imgur.com/ycKWd2p



What did Tim say: “....="" in B1:B23, then coied that range and pasted values in it. ....”
180 Rem 2) ( I do try to make Worksheet “1” like Worksheet “2” )
' I do first try this ....
‘2a)
204 – 220 I get VBA to write ="" in B1:B23, then
240 I copy that range and _ range("B4:B23").Copy
250 paste values in it. ___ range("A4:A23").PasteSpecial Paste:=xlPasteValues
If I stop the code at any of the next few lines, I will find Excel’s helping with “Automatically repeat values that already exist in a column” in A4:A23. I expect this: This is because we have something, zero length Strings , in between rows. CellContentInTinSheet1NowZeroLengthStrings.JPG http://imgur.com/ylfBZgn
( An Empty row would normally prevent Excel’s helping with “Automatically repeat values that already exist in a column” from working further )

But I did not expect tim Worksheet 2 to do this. I did not expect Excel’s helping with “Automatically repeat values that already exist in a column” in A4:A23. I did not expect this because the cells in between are Empty in tim’s Worksheet 2
??????????


310 '2b) UsedRange is Reliable. But... We must work hard to understand it.*** It is not accurate ?????
Excel has a memory of UsedRange. It is reliable. ....

Originally Posted by tim201110

.. example of VBA property Usedrange..... it is not accurate....

.??. I think possibly it is accurate.
I think this trick is not concerned with UsedRange
I look again at Worksheets “2”.
314 I look again carefully at column B
I find that range B4:B23 is not Empty It contains zero length Strings. !!!!
_( The “trick” I slightly miss understood ___ -:-( , so look again...
316 I clear what I put in column A ___ ( - I expect that tim had not meant to paste in column A - ) and
318 paste in zero Strings in range B4:B23 ( - I expect tim had meant to say paste in column B - )
( 320 cells are now Empty in column A )
325 cells are now full with zero length Strings in column B
340 ' Excel's still helping with "Automatically repeat values that already exist in a column", also
350 We have 23 for last UsedRange row
But this is not memory of UsedRange. - UsedRange includes column B. It is not remembering ( in line 350 ) UsedRange***. It is measuring the UsedRange row from column B which is filled with zero length strings
352 ' Worksheet "1" is now finally like Worksheet "2"

The explanation is CurrentRegion, and that it appears that Excel’s helping with “Automatically repeat values that already exist in a column” is bounded by Current Region http://www.excelforum.com/excel-gene...ml#post4479069


354 Rem 3) Current Region
Continued in next Post

Excel's "Automatically repeat values that already exist in a column" works over CurrentRegion

354 Rem 3) Current Region

Current Region is the smallest contiguous ( all there, none missing ) single Areas range that can be enclosed within a perimeter made up of the Worksheet boundaries, or an empty rows or columns.

Currently for the Worksheets as I have them now, this looks like this, ( where Yellow is the rows over which Excel’s helping with “Automatically repeat values that already exist in a column” is bounded and works. This is bounded by the smallest possible contiguous ( all there, none missing ) single Areas column range that can be enclosed within a perimeter made up of the Worksheet boundaries, or an empty rows or columns, because over range B4:B23 the cells are filled with zero length strings, which forces the upper row boundry to lie in row 24.

Row\Col	A	B	C
1	Freddy
2	For
3	Ever
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Worksheet: 1

So I suggeste that the upper boundary of Excel’s “Automatically repeat values that already exist in a column” is simply set by CurrentRegion.
To demonstrate this I will remove an arbitrary zero length string form column B, say in row 7. I will make B7 Empty. ( Using _ .ClearContents _ should be sufficient )
355 Before doing this if I check, I will find that Excel’s “Automatically repeat values that already exist in a column” is working for the range up to row 23 ExcelsAutomaticallyRepeatValuesCurrentRegionToRow23.JPG http://imgur.com/jlvfAk1
356 I also check that the cell B7 has a zero length string in it: B7ZeroLengthString.JPG http://imgur.com/HkzW41R
357 I remove the zero length string in cell B7 only.
360 I see that cell B7 is now empty. B7Empty.JPG ___ http://imgur.com/3GqzAXJ
370 If I now check, I see that that Excel’s “Automatically repeat values that already exist in a column” is working now only up to row 6 ExcelsAutomaticallyRepeatValuesCurrentRegionToRow6.JPG http://imgur.com/LDjUnKp

The following screenshot shows the current situation regarding the CurrentRegion being now the range A1:B6 , and consequently the range of “Automatically repeat values that already exist in a column” being range A4:A6

Row\Col	A	B	C	D
1	Freddy
2	For
3	Ever
4
5
6
7
8

Worksheet: 1
_...

So the key and answer to this little trick is two fold..
_ noting that Microsoft documentation can be very bad on these things, and so following on from that:
_ we need to work very hard so as to share knowledge effectively
We must work hard sometimes to share our knowledge effectively
_ So ‘ I want to live forever
http://listenonrepeat.com/watch/?v=WvA4hrpCSew#Queen_-_Who_Wants_to_Live_Forever_(1986)
because
‘ so here I go again http://listenonrepeat.com/watch/?v=W...I_Go_Again__87
‘ and then we can be Heroes, like Fredy is mine http://listenonrepeat.com/watch/?v=j...Bowie_-_Heroes ... for lots of Days .. .for ever and ever ..what tcha say? “.. ...I wish I .. could swim, ... .... like Dolphins .. can swim ..”

‘_- ..........___________________




***Some further notes in next post on UsedRange and UsedRange memory in the next post.

Excel tricky Range Special Mental last Cells. UsedRange Memory

Excel Range.SpecialMentalCells and UsedRange in LastCell

There are some peculiar memory of UsedRange to be aware of: This is demoed from Rem 4) in the last discussed code ( https://www.excelforum.com/developme...ml#post4632633
https://www.excelforum.com/developme...ml#post4632636
( Code is in two parts to fit in due to post size limitations but it is all one code )
)

400 From the last action in the code in Rem3) ' Excel's still helping with "Automatically repeat values that already exist in a column" up tp row 6.
410 One way is shown to determine the last row http://www.excelforum.com/excel-prog...ml#post4486473 The way shown in line 410 uses the last cell option argument for VBA Range.SpecialMentalCells Method ( https://msdn.microsoft.com/en-us/lib.../ff196157.aspx ) way.
( In this case it returns us 23 , as expected ).
This way of obtaining the last row has a peculiarity in that it works on the Memory held by Excel of the last UsedRange. This is demonstrated as follows , along with showing that that Excel’s “Automatically repeat values that already exist in a column” is relatively well behaved in this respect.

430 We clear all but our three cells of text in A1:A3 , and then as expected, Excel would only help us with automatically repeating values that already exist in a column for cell A4.
We know that the last row in our UsedRange is now 3. But....
450 – 470 The last row determined from the last cell option argument for VBA Range.SpecialMentalCells Method way still returns us 23, and not 3 as we may have expected.
This is because VBA Range.SpecialMentalCells Method in general uses the Memory of the Last UsedRange.
This is somewhat difficult to prove, because any usage of a UsedRange Property will refresh this Memory. So for example line
490 gives us the correct last used range row of 3, but in doing so , as a by-product, also refreshes the memory of UsedRange. So in the next line
500 we obtain now the correct last row of 3 from the the last cell option argument for VBA Range.SpecialMentalCells Method way

' Rem Ref:
https://www.excelforum.com/excel-new...cified-ra.html


We must work hard sometimes to share our knowledge effectively
_ So‘ I want to live forever
http://listenonrepeat.com/watch/?v=WvA4hrpCSew#Queen_-_Who_Wants_to_Live_Forever_(1986)
because
‘ so here I go again http://listenonrepeat.com/watch/?v=W...I_Go_Again__87
‘ and then we can be Heroes http://listenonrepeat.com/watch/?v=j...Bowie_-_Heroes ... for lots of Days .for ever and ever ...what tcha say? “.. I wish I could swim, ... like Dolphins can swim ..”

‘_- ..........___________________


P.s.
I suggest a simple Function to increase Excel’s automatically repeating values in a column to further cells would be one that fills all Empty cells in the column range one wishes to have available Excel’s automatically repeating values in a column to further cells with a zero length string. The following Calling routine , Calls a simple function, passing the selection as Range object. The Function puts _ = “” _ in Empty cells , and then, following along the ideas in this Thread, it copies those and re pastes in just the values

Please Login or Register  to view this content.

( Simpler still could be to put any text into the Empty cells and colour them white, or light grey. Of course the zero length string is the best trick if you want to confuse someone ... and putting it in an adjacent column is even better... )

What is a bit peculiar is
_ that if I assign vbNullString to the Empty cells, then they stay Empty???
_ If, on my final cells containing a String of zero length , I do the _..
_ Let Rng.Value = Rng.Value
_.. thingy, then the cells are Emptyied.
_( similarly doing that on a formula in a cell or cells of _ = “” _ also Emptyies the cell or cells )

So somehow writing a null string to a cell does not work, whereas , as we have seen in this thread, copying a formula of _ =”” _ and pasting the value of that into a cell does result in a zero length String in the cell

Strange .... No wonder why Microsoft documentation is sometimes so crap, - I doubt they know themselves sometimes what is going on.

Alan

Unless I missed it in one of the above posts Format Painter works, too.

Numbers 1-9 in A1:A9.
To B1:B3, B4:B6, B7:B9 apply center and merge.
Select B1:B9. Click Format painter and apply to A1.

Something else wicked to be alert for!!

Merged cells actually are the devil's spawn. Q.E.D.

Hi Dave,

Originally Posted by FlameRetired

.... Format Painter works, too.
Merged cells actually are the devil's spawn. Q.E.D.

Ah, I missed that.. - an easier way to paste the formats in.
I think the person who invented merged cells wanted to vent some anger on Excel VBA Programmers. Some programmer must have done something really bad to him.
Alan

No. It's the devil Alan. They must have done something really good. LOL

My brother in Law just read my last few posts here, and said he liked the songs, was interested in using Excel’s characteristic of prompting you with words already in a column when you start to write them... But he did not understand a word. ( Well he is German ).
I told him it was probably working by accident as a result of a mechanism in place related to the Current Region Property. But he still did not understand.

He understood the following explanation.
So it might help someone else get the point quicker:
Here you go... Bro...

Question:
In the following screen shot, over which cells will Excel prompt me with SomeFink, if I write _ s _ in the cell ?

Row\Col	A	B	C	D	E	F
1
2
3		a
4			b
5				c
6	SomeFink				d
7				e
8			f
9		g
10
11

Answer:
.... If we define the characteristic as prompting you for existing entries in a column of entries, when you type in the next empty row of a column range, then that column range is that bounded by the Current Region associated with the existing entry or entries.

The Current Region in this respect, in Excel, is defined , in simple terms, as a single area of spreadsheet cells which contain the existing entry or entries, and which can be enclosed by a combination of any spreadsheet boundary and an empty row and an empty column
Saying it a bit differently: Excels characteristic of prompting you for an Entry already included in a column is limited to the Current Region associated with an existing Entry or Entries. Within and at ( touching ) that boundary Excel will prompt you for an Entry already included in the column range thus bounded

For the above screenshot , the Current Region bounded by a combination of any spreadsheet boundary and an empty row and an empty column looks like this , ( bounded by two empty rows , a empty column, and the left vertical spreadsheet boundary :

Row\Col	A	B	C	D	E	F	G
1
2
3		a
4			b
5				c
6	SomeFink				d
7				e
8			f
9		g
10
11

In the range shown here in yellow, Excel will prompt you for SomeFink, is you type in _ s _ in either of those cells

Row\Col	A	B
1
2
3		a
4
5
6	SomeFink
7
8
9		g
10
11

_........

That column range mentioned above would be this, ( at least based on the part definition that the adjacent rows have the characteristic as prompting you for existing entries – For A6 no suggestion would be given )

Row\Col	A	B
2
3		a
4
5
6	SomeFink
7
8
9		g
10

And last but not least, don’t forget Sebastian Bach..
http://listenonrepeat.com/watch/?v=i...I_Remember_You




P.s.
There is slight subtlety/ flaw in this little theory . If no entry touches on the column of interest in any direction, then there are no longer any extended rows other than the first in which Excel will prompt.
In the following slightly modified screenshot, only in the first row in the up direction will the Excel prompt work.

Row\Col	A	B	C	D	E	F	G
1
2
3			a
4			b
5				c
6	SomeFink				d
7				e
8			f
9		g
10
11

So I guess the statement should be modified to: If we define the characteristic as prompting you for existing entries in a column of entries, when you type in the next empty row of a column range, then that column range is that bounded by the Current Region associated with the existing entry or entries in any vertical direction, provided that there is a connection to that column with filled cells within the Current Region. In the absence of such a connection in any direction, then in that direction that column range will reduce to the entries in the column. But that is not quite right either!!!

z			a
c			b
	SomeFink
				e
			f
		g

P.s.....

(Note additionally above, an entry just under _ c _ ( or 1 up and 1 to the right of SomeFink will extend that range 3 rows up). !!! Without the presence of _ z _ that range will only extend two rows up. So it would appear that additionally at least one cell adjacent to the column must have an entry in the last row in that range and that must be “connected” through cells to the last entry in the column. So the Current Region explanation is not quite the full story either..
I guess the reason there is no documentation on this is because, like many things in Excel, there are a lot of little quirks that no one really understands, or at least possibly no one at Microsoft.
It is probably working on a by product of some mechanism in place related to the Current Region Property,

Just looking at it graphically, empirically , no explanations:

Green is that area ( single “breadth” range, single column range or continuous cells ) over which 2 criteria are met:
_ Excel will help you and suggest a word already in that column if you begin typing the word
_ in the next cell in to that area either the up or down direction Excel will help you and suggest a word already in that column if you begin typing the word

Light Yellow is that next cell


The simple cases first, that most of us know:

	SomeFink

	SomeFink
	SomeFink

The next one we may have gleaned from the last few posts

		t
		h
		k
	SomeFink

This may have gone overlooked due to the reduced size range used in the last posts – note the characteristic not working in the “in between” rows

		v
			t
				n
		Bee			m
				n
			b
	SomeFink

Adding the “Connection” to the “Bee” above makes “Bee” active as it were in doing its job.., and we have then the characteristic working over a continuous range, but it is being “fired” from two points. – Consider “Bee” and “v” some sort of power connections that only extend in there effective working range to one cell up and down

		v
			t
			Connection	n
		Bee			m
				n
			b
	SomeFink

_.......................

And this is rather curious: Once we have a live green range then the entire row or rows connected to that range become “live” and can be tapped off to make another live green cell . ( Note rows corresponding to any existing entry are also effectively “live” rails from which we can connect to make a cell live ). In the following screenshot, the n’s are one connection route , and the m’s the other. The m and n’s are connection strings or strings of connection

		n	n	n
					n
					n
					n
		m
			m
				m
	SomeFink

_.....

Another interesting example.
Consider first a single mmmm connection string making two connections to the column

		m	m	m
				m
				m
			m
		m
			m
				m
	SomeFink

We could consider those two connection points to the column as working due to the unbroken mmm command chain, or alternatively as the upper green section feeding of the live row caused by the lower green section.
Removing one of the m’s as shown in the below screenshot breaks both the connection command chain as well as the connection to the first green section. So our characteristic is gone for all but the original entry

		m	m	m
				m
				m
			m
			m
				m
	SomeFink

_..
Here comes something interesting...
Adding any entry to an adjacent cell appears to make a row “live”.
In the following screenshots I have made the row from which I removed the m “live”. Because of this I can tap off it for my upper green area. But I have not completed a connection to the original lower green area ( Note: you need the lower 2 m’s connection to that “rail” )

		m	m	m
				m
				m
			m
anyfink
			m
				m
	SomeFink

		m	m	m
				m
				m
					m
anyfink
			m
				m
	SomeFink

_._______________________--

You could think of a good Water Cooler Board Game based on the findings here... you type in certain cells and if you hit on the wrong cell then can get “caught” by Excel prompting a message like “you are dead”. Once that word is accepted, that can itself make a row now “live” from which existing connections would make, for example, new traps..

Or the idea in the last three screenshots shows how you can set a trap inadvertently by filling in a cell with anyfink. .. etc... etc....

Connections can be made invisible using the zero length string ideas from a few posts back, so you cannot see them.

_...

I have a theory based on a combination of Interception theory and mechanisms in place associated with the CurrentRegion to explain all this... . But I probably will spare us all that entertainment...

But as a taste of what’s not to come:
oADOpey Connection Wonks:
_ Light Blue is my data file in computer memory, similarly
_ Making that oConn takes a few seconds to build up in the practice as I might have to muck around a lot to get where I want to be. ( Some intelligent routines will be needed to navigate differently in different systems. The data file and location is needed and must be given therefore)
_ To open the data as a record set in the , making the rConn is not to difficult as the data is by internationally agree standards mean those “positional” occurrence of “rConn” have a well pre defined Pattern.
_ The command string will include the referencing of which ever data is wanted based on the F1 , F2 etc. ( or alternatively as specific headings , in which case the sConn as instructed in its string , sConnString, for HDR=Yes, will do some simple adjustment )

My CommandString will take the form of SELECTing _ F1, F2 _ FROM _ LightBlueColumnRange,
then I will open up a rConn record set based on
Opening a rConn _ Using CommandString , _ On Opened oConn
With F1 selected gets me the Veg, with both selected I get both .
Without rConn no data can be in the available record set rail. Which F’s are selected determine which data appears in the record set. Not shown here is the possibility of getting all record set data in one go. So it is not a perfect comparison / model to what is going. But we do have the data physically going along “the” column. Agreed there are more than one column, but offsets in column could be compared to offsets in time... In addition the actual connection is a single string as you go through the data, which leads on to the next limitation...
In addition, my selection is limited to F1 or F1, F2 . I have not figured out a way yet to make F2 on its own available. ***. However, some of my speed measurements suggest to me that in the practice in typical data retrieval, all up to the maximum column would actually be retrieved, and some post processing is used to remove the unwanted ones.. but I am not totally sure about that.

Note interestingly that the upper rConn is adding the Veg column to the “live rail”. The effective “F2 live rail” is connecting there I expect

Some rules for this to work / requirements of the computer memory will be
_ that the rComm row and that row below it ( the header row ) must be clear of all other data.
_ care must be taken to keep the oConn isolated, or possible leakage paths can cause data availability where not expected or wanted.

That last lot is all a bit imprecise and abstract , so :
Just putting that in English. As it as in the shown Worksheet below, Excel will prompt you for the Veg and Meat entries if you start typing any of those food products in the green/light yellow 4 cell range. If you remove F2 then meat products will no longer be offered by the prompt. Removing F1 makes all products unavailable ***)
Without the rConn’s no data is available.

	Data in / from File via direct Interception relative reference	x	Data in / from File via direct Interception relative reference	Computer x	Clutter for x	oConn to x	Navigate x
		x				x
		oConn		oConn		x
		oConn		oConn			x
	oConn	oConn	oConn		oConn
						oConn
		x		x		oConn
				x		oConn
rConn
			F1
			Tomato		x
		x	Potato	x
rConn						x
	F2
	Sausages
	Beef

Alan

Does anybody know how to concatenate text from a column into one cell without using formula or VBA with only one click?

Best of luck to all of you

From this

v	A
1
2	One,
3	Two,
4	Three,
5	Four,
6	Five

to this

v	A
1
2	One, Two, Three, Four, Five

Alan, you took the matter so seriously

- I took something seriously ! -
How very strange

_..._____________________
@ AlKey
Your supposed to tell us the answer... lol... but I will sleep on it ...

Originally Posted by tim201110

=CONCATENATE(TRANSPOSE(A2:A6&" ")) as arrayformula, select a formula and press F9
in a cell:
={"One, ";"Two, ";"Three, ";"Four, ";"Five "}

Remember: "without use of formula or VBA"

Originally Posted by tim201110

not one click, through clipboard. Yes?

No

Ok, I am not going to torture you:

Make sure that column A is about 4 inches or 10 cm wide.

Select range A2:A6, Go to Fill button located on Home tab and choose Justify from the menu.

Justify.JPG

Well I do not seem to have Justify in my XL 2007 or XL 2010 ??
So I was snookered
( but anyway it was a Select and two clicks... )

Originally Posted by Doc.AElstein

Well I do not seem to have Justify in my XL 2007 or XL 2010 ??
So I was snookered
( but anyway it was a Select and two clicks... )

1). The button is there just right under the SUM icon and it is present at least in Excel 2010, 2013 and 2016.
2). One click is to click on that button and press J on your keyboard. Range selection was done by keyboard as well.

**and to confirm that Justify is available in Excel 2007 as well.

If I compare my XL 2010 and XL 2007 with this:_....
http://excelunplugged.com/2014/06/17/fill-and-justify/
Justify.JPG http://imgur.com/LXGQaMV


then I see this in my XLs:
MissingJustify.JPG http://imgur.com/lz44hNU

So it does not seem to be there


Nevermind... I still think it was more than one click .. even if it was just one click... and a press .... and a keyboard... Lol...

Hi Alkey

Originally Posted by AlKey

1). The button is there just right under the SUM icon and it is present at least in Excel 2010, ....**and to confirm that Justify is available in Excel 2007 as well.

can you do me a screenshot of it in XL 2007 or 2010 as you see it. I can't seem to see it anywhere.
Thanks
Alan

Originally Posted by Doc.AElstein

Hi Alkey
can you do me a screenshot of it in XL 2007 or 2010 as you see it. I can't seem to see it anywhere.
Thanks
Alan

It is at the same location as it shown in post #34
https://www.excelforum.com/the-water...ml#post4637176

Here is an additional info about Fill button and available functions
http://www.uwec.edu/help/excel07/ws-fills.htm

Originally Posted by AlKey

It is at the same location as it shown in post #34.... additional info about Fill button and available functions
http://www.uwec.edu/help/excel07/ws-fills.htm

OK, thanks.
Alan


Its not there in mine:
XL 2007
MissingJustify2007.JPG http://imgur.com/AtTG73U

XL 2010
MissingJustify.JPG http://imgur.com/oVeSQzb


EDIT wot a tw t I am - must learn my German
Got It !!
JustifyGotIt.JPG http://imgur.com/tdutBvc

Maybe you using a limited version of Excel Or maybe Germans did not like this option.

Originally Posted by AlKey

Maybe you using a limited version of Excel Or maybe Germans did not like this option.

No I was using a limited version of a German brain , mine!!
Got it ( I edited the post )

Congratulations! Thanks for the rep