Excel Justify

[Brad]

It is my understanding that Excel will not Justify if the range has equations
in it - True?

Assuming that my previous statement is true I was going to have a helper
column in "A" and have my output in "L" and then put in the following logic
(found after my questions). One item that I discovered was if one line was
completely blank it would not "move the text" up. But if I added a " ", the
justify would work.

Three questions - is there a better way to get around the formula issue?
Are there other known issues (other than a blank line) that I should be on
the look out for? If there is not a better wary to get around the formula
issue - is there better logic than the code below?

Sub test()
shtCB2D.Range("L35:L39").Value = shtCB2D.Range("A35:A39").Value
shtCB2D.Range("L35:U39").Justify
End Sub

 

[sebastienm]

Hi,
'Justify' works for me, even on cells containing a formula.
Could you please give an example with Text, Column size and Row size.

 

[Brad]

Column width of "B" and "C" 8.43
An example - in Cell B2

="Here is the value "&A2&" and the example doesn't work"

Fill - justify B2:C8 gives the error message

"Cannot justify cell containing numbers or formulas."

 

[sebastienm]

I see, I am getting the same message as you do.
Earlier, I was using a different code, so would that work for you?
shtCB2D.Range("A35:A39").HorizontalAlignment = xlJustify

 

[Brad]

When I put in your code and try to modify it to go across multiple columns -
it just wrapped the text in the first column specified.

 

[sebastienm]

I have just realized we have a different definition of 'Justify'.

To me Justify is the same as the toolbar icon 'Justify' (similar to Left,
Right, Center alignment; you can get it through Toolbars>Customize, category
Format > Justify) , also the same as in MS Word. 'My' Justify, wraps the text
and aligns both right and left sides by re-arranging the spacing between
characters.

I have just ran your code on a non-formula cell and I see what you mean now.
I had never used/heard of this feature before, sorry.
Interesting though.

I played a bit with Justify and searched around, i couldn't bypass the
formula issue. I couldn't find any method different that your code.
To replace the blank cells by spaces before Justify (but you've probably
already figured that one out though)
shtCB2D.Range("L35:U39").Replace chr(0)," "