# Off Topic > The Water Cooler >  >  Small trick in excel

## tim201110

Hi,
There are hidden values in the merged cells in the attached file. One can't see them, but they are calculated. No names, no VBA, nothing.
If you know something like that, please post it here.
just for fun.

----------


## Glenn Kennedy

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

----------


## tim201110

It is easy. A hint is in my signature.  :Smilie:

----------


## Glenn Kennedy

Duhhh.... Not that easy...

----------


## tim201110

OK. Copy the range to somewhere, merge cells in the new range as you wish, copy it and paste formats in the old one. clear new range. Voila

----------


## Doc.AElstein

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  :Smilie: 

 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  :Frown:  ).
_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 *E*aste*r* *E*gg also: 
Using Excel 2007 32 bit
Row\Col
A
B
C
D
E

*5*

Heading1
Heading2
*E*gg
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  :Smilie: 
Ha*p*py *E*aste*r* 

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)

----------


## tim201110

Thanks!
Here is one more trick.
There a nice feature, it is autofill. It works great, but you skip a row or more Excel keeps silent.
The file in the attachment proves otherwise.
If you try to input a string from the above cells  into column A on sheet 1, excel won't help you.
Sheet 2 is different.

----------


## Doc.AElstein

Sorry, I do not understand at all  :Frown: 
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
 :Confused:

----------


## tim201110

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

----------


## Doc.AElstein

> 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 ?  :Smilie: 

Alan

----------


## tim201110

Sure, I am not a magician so far.  :Smilie: 
I input ="" in B1:B23, then coied that range and pasted values in it. Voila.
And last, but not least.
One can not input arrayformula into merged cell.
In the attached file arrayformula in B1 works.

----------


## sanram

Thanks for sharing such a nice trick.

----------


## Doc.AElstein

Hi tim, 



> 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  :Smilie: 
Off Topic Forum.JPG http://imgur.com/qOvV2vA 
Off Topic Forum2.JPG http://imgur.com/eiB8pqt

Have a good Easter Sunday

Alan

----------


## tim201110

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.

----------


## Doc.AElstein

:Roll Eyes (Sarcastic): 
Hi tim, 
OK.  :Smilie: 
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   :Smilie:   )

( 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   :Smilie:   )

_...._______________

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*

----------


## tim201110

One can explain everything, but miracle has been so wonderful.  :Smilie: 
https://youtu.be/2DaY8-Mui0I

----------


## Doc.AElstein

> 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 ) 



> ...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   :EEK!:  - 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!!!*   :EEK!:  
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*  :EEK!:  
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. .... 



> .. 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

----------


## Doc.AElstein

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 _ ..”  
 :Smilie: 
_‘_-  ..........___________________
_



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

----------


## Doc.AElstein

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 _ ..”  
 :Smilie: 
_ ‘_-  ..........___________________
_

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*

 :Smilie:

----------


## FlameRetired

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.

----------


## Doc.AElstein

Hi Dave,




> .... 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

----------


## FlameRetired

No. It's the devil Alan. They must have done something really _good._ LOL  :Smilie:

----------


## tim201110

Did you know it?
It is a usefull feature.
One can press F2 and change a link this way, just drag it in F2 mode.
https://www.youtube.com/watch?v=pGvcCRTpTvc

----------


## Doc.AElstein

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

----------


## Doc.AElstein

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,*

----------


## Doc.AElstein

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

----------


## tim201110

Alan, you took the matter so seriosly.  :Smilie: 
I think without Excel (and .xls) source code one can acheives almost nothing.
Nobody knows what electricity is, but everyone uses it  :Smilie:

----------


## AlKey

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 :Wink: 

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

----------


## Doc.AElstein

> Alan, you took the matter so seriously



 :EEK!:   :Confused:   :EEK!:   - *I* took something seriously !  -  :EEK!:  
How very strange    :Confused: 
 :Smilie: 
_..._____________________
@ AlKey
Your supposed to tell us the answer... lol... but I will sleep on it ...  :Smilie:

----------


## tim201110

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



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

----------


## AlKey

> =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"

----------


## tim201110

without using formula or VBA
Oops!
AlKey, please show result rows 3-6

----------


## tim201110

not one click, through clipboard. Yes?

----------


## AlKey

> not one click, through clipboard. Yes?



No :Smilie: 

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

----------


## Doc.AElstein

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

----------


## tim201110

Class!
wish I could use it

----------


## AlKey

> 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.

----------


## Doc.AElstein

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...  :Smilie:

----------


## Doc.AElstein

Hi 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.  :Confused: 
Thanks
Alan

----------


## AlKey

> 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

----------


## Doc.AElstein

> 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.  :Smilie: 
Alan


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

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

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

----------


## AlKey

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

----------


## Doc.AElstein

> 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 )
 :Smilie:

----------


## AlKey

Congratulations! Thanks for the rep :Smilie:

----------


## tim201110

Дело было вечером, делать было нечего.
It would be better if the attached WB was the only one open in Excel
Press F9 or input any number in A1 and press F9

----------

