Excluding cells from autofit

[tsh73]

Here's my problem.
I have big table inserted from FoxPro;
and it would be nice to autoFit columns based on numbers in it.
But I also have some group rows; it has rather lengthy text(compared with numbers that is).
If I put them in single cell then this (first ) column will be too big (after autofit).
If I join cells - autofit doesn't "tackle" them (this is good) but I cannot select column (range) ecross groups (to see sum etc).
If I join only two first cells in a row (so most columns will be selectable) then group text gets cut by a column!

so I wish I know how to exclude some cells in a column from autofit.

Any ideas?

 

[Loomah]

Hi
This is a REALLY rough idea of how it might be accomplished.

This line of code will set the width of column A according to the value in cell K1 - there's no signifigance in that cell it's just what I used!!

Columns(1).ColumnWidth = [k1]

The value in K1 comes from a formula (this is where my VBA lets me down as I don't know how to write this into code without having to put it in a cell!). The formula finds the maximum length of the cells excluding the first row. It is entered as an array formula, ie CTRL+SHIFT+ENTER

=MAX(LEN(A2:A65536))

If you have more than one row with 'long' text than this won't work!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Loomah - summat I just found out about might help you here - although possibly not as it is an array formula - and it probably won't work for them...

If you define a formula as a name - ie enter the formula into the "Refers To" box in the Insert>Name>Define menu, you can call that formula fromj code, using the EVALUATE function or syntax eg

Name is myForm

FormulaResult = [myForm]

FormulaResult = Evaluate(names("myForm"))

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
Coincidences - I'd just found out about it myself literally 2 minutes before seeing your post.

In the context of this thread it will work as an array
ie

Columns(1).ColumnWidth = Evaluate("=MAX(LEN(A2:A1000))")

works!

Just had a little look at the help file and apparently using square brakets ([]) works too so this is ok

Columns(1).ColumnWidth = [MAX(LEN(A2:A1000))]

Wey-Hey, new toy, new toy!!
Which I'll probably have forgotten about in a week!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​