Greetings,

I have an alignment problem across several cells. For this question
let's say the area is A10 to Z10. Let's name the area "TestArea1".

When I highlight "TestArea1" and go to the format menu and choose the
Alignment tab, the only choice that does anything is "Center Across
Selection". This will not work in this situation! I need the text to
start on the left side of "TestArea1", not the center.

This can be done by merging the cells in "TextArea1", which is
something that I am trying to avoid, if I possible.

Anyone have any ideas as to how this can be done?

Any help will be appreciated.

-Minitman

You could merge the cells but that can cause many problems.

Would it help to move all the data into A10 then leave B10:Z10 blank?

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:Z10) entered A11

Copy and paste values to A10.........delete B10:Z10


Gord Dibben MS Excel MVP

On Thu, 17 Jul 2008 21:39:43 -0500, Minitman
wrote:

Greetings,

I have an alignment problem across several cells. For this question
let's say the area is A10 to Z10. Let's name the area "TestArea1".

When I highlight "TestArea1" and go to the format menu and choose the
Alignment tab, the only choice that does anything is "Center Across
Selection". This will not work in this situation! I need the text to
start on the left side of "TestArea1", not the center.

This can be done by merging the cells in "TextArea1", which is
something that I am trying to avoid, if I possible.

Anyone have any ideas as to how this can be done?

Any help will be appreciated.

-Minitman

Hey Gord,

Thanks for reply.

Thanks for the code. I have not used functions as yet. Two
questions, how and where do I set and use this function? (I can no
longer use msHelp since it gliched and is coming though at about 4
point print and distorted. Someone else had the same problem and no
one offered any suggestions, I assumed it was going to be one of those
unanswered questions.)

-Minitman




On Thu, 17 Jul 2008 19:55:22 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

You could merge the cells but that can cause many problems.

Would it help to move all the data into A10 then leave B10:Z10 blank?

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:Z10) entered A11

Copy and paste values to A10.........delete B10:Z10


Gord Dibben MS Excel MVP

On Thu, 17 Jul 2008 21:39:43 -0500, Minitman
wrote:

Greetings,

I have an alignment problem across several cells. For this question
let's say the area is A10 to Z10. Let's name the area "TestArea1".

When I highlight "TestArea1" and go to the format menu and choose the
Alignment tab, the only choice that does anything is "Center Across
Selection". This will not work in this situation! I need the text to
start on the left side of "TestArea1", not the center.

This can be done by merging the cells in "TextArea1", which is
something that I am trying to avoid, if I possible.

Anyone have any ideas as to how this can be done?

Any help will be appreciated.

-Minitman

If you're not familiar with VBA and macros/functions, see David McRitchie's site
for more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Follow the steps for usage of the Function per my first post.


Gord

On Fri, 18 Jul 2008 00:21:09 -0500, Minitman
wrote:

Hey Gord,

Thanks for reply.

Thanks for the code. I have not used functions as yet. Two
questions, how and where do I set and use this function? (I can no
longer use msHelp since it gliched and is coming though at about 4
point print and distorted. Someone else had the same problem and no
one offered any suggestions, I assumed it was going to be one of those
unanswered questions.)

-Minitman




On Thu, 17 Jul 2008 19:55:22 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

You could merge the cells but that can cause many problems.

Would it help to move all the data into A10 then leave B10:Z10 blank?

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:Z10) entered A11

Copy and paste values to A10.........delete B10:Z10


Gord Dibben MS Excel MVP

On Thu, 17 Jul 2008 21:39:43 -0500, Minitman
wrote:

Greetings,

I have an alignment problem across several cells. For this question
let's say the area is A10 to Z10. Let's name the area "TestArea1".

When I highlight "TestArea1" and go to the format menu and choose the
Alignment tab, the only choice that does anything is "Center Across
Selection". This will not work in this situation! I need the text to
start on the left side of "TestArea1", not the center.

This can be done by merging the cells in "TextArea1", which is
something that I am trying to avoid, if I possible.

Anyone have any ideas as to how this can be done?

Any help will be appreciated.

-Minitman