Left align text in joined cells

[HTSCF Fareha]

I have the following code to "join" the top three cells on a worksheet, then place some text inside this. I just cannot fathom out how to get the text left aligned (just for the joined cells).

Another strange phenomena that I am experiencing is that the text seems to be invisible on the worksheet, but magically reappears when all the data is pasted into a Word document.


' Join the top three cells
ActiveCell.EntireRow.Range("A1:C1").Select
Selection.HorizontalAlignment = xlCenterAcrossSelection


' Add line of text
ActiveSheet.Range("A1").Value = "This is the history shown for the past eighteen months :-" & vbCrLf

[jolivanes]

You're alignment is "CenterAcrossSelection" so that's what you get.
Without it, if you enter text in cell A1, it spills over into the next cells so that would have the effect of left align.
Why are you "joining" the three cells then?
If you can't see the text, you must have some other formatting in there. Font formatted to white maybe?
Merge and Center with a starting line feed character and the row height restricted?
Just thinking out loud here.
Have you tried copying and pasting (PasteSpecial, Values) the data into a new sheet to see what the result of that is?

The best would be to attach a copy of the problem workbook without any private data.

[Paul_Hossler]

You can try this, but personally I don't like the results since the text is cut off within the row

You have to adjust the RowHeight to see it

Usually, I just put the text into A1 and left it flow o the right

What format are you looking to end up with?




Option Explicit


Sub Macro1()
ActiveSheet.Range("A1:C1").Merge
With ActiveSheet.Range("A1")
.HorizontalAlignment = xlLeft
.WrapText = True
.Font.ColorIndex = vbBlack
.Value = "This is the history shown for the past eighteen months :-" & vbLf
End With
End Sub

[HTSCF Fareha]

Thanks for the responses and advice. I've had a rethink and will leave things so that the text is entered in cell A1 with no other cells being used. :thumb

[jolivanes]

Put this in the Sheet Module for that sheet. That way nothing can be entered in Cells B1 and C1. It will take the overflow from A1 though.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B1:C1")) Is Nothing Then
Cells(ActiveCell.Row, 1).Select
End If
End Sub