Text spacing within a cell

S

susanlayton

I use spreadsheets to provide product descriptions, etc for clients. To
create a spreadsheet that is easy on the eyes, I would like to leave
one blank line of space at the top of each cell and another blank line
of space at the bottom of each cell.
I do not want to go into each cell (or row) individually to do this,
but would like to find a way to automatically format cells to do this.
It would be equivalent to "space before" and "space after" a paragraph.
Any ideas????
 
P

Phillip M. Jones, CE.T.

I use spreadsheets to provide product descriptions, etc for clients. To
create a spreadsheet that is easy on the eyes, I would like to leave
one blank line of space at the top of each cell and another blank line
of space at the bottom of each cell.
I do not want to go into each cell (or row) individually to do this,
but would like to find a way to automatically format cells to do this.
It would be equivalent to "space before" and "space after" a paragraph.
Any ideas????
Click to expand...

Format menu > Height make vertical spacing two three time normal then
while highlighting all the cells in the workbook got to edit cells and
set vertical for font centered vertically.

Another thing you can do is just format the color of every other row
some type of color that way the viewers can keep track of what
information is on what rows.

If you need every other column highlighted just highlight every other
column and highlight with a color.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto:p[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>
 
S

susanlayton

Thanks for your response.

The number of lines of text per cell varies - so selecting all the
cells and then adjusting the height will not work.
The solution I am looking for would actually add a blank line before
the text and after the text without touching every cell (row).
Thanks again for your suggestions.
 
J

jimdilger

You can achieve the effect of a space before and space after by
enlarging the height of a row and then centering the text vertically.
Ideally, you should be able to define this as a "format" and then
simply apply the format to other cells. However, "format" does not have
cell height as a parameter (it does have text centering, though). You
would have to adjust the height of selected rows separately.

You could also make a simple macro to do this. Select a cell that you
want to format this way.
1. Start the macro recorder with Tools>Macro>Record new Macro... and
provide a convenient name (e.g. EasyToRead). Select: Store this Macro
in "Personal Macro Workbook" so it will be available in all
spreadsheets.
2. Change the row height with Format>Row>Height...
3. Change the vertical alignment with Format>Cells>Alignment (tab).
4. Stop the recorder. (hit the stop button on the floating macro
toolbar).

Run the macro whenever you want to get this formatting.
 
J

jimdilger

You can use Command-Option-Return to force a line feed. When you start
typing in a cell, do a Command-Option-Return and then when you are
finished, do it again. Then a regular <return>.
 
S

susanlayton

Thanks for your reply.

Please understand that the cells contain a variable number of lines.
Formating the rows to be a uniform height is a simple operation - but
that's not what I am trying to do. No matter how many lines of text -
1 or 21 - I want to add a uniform amount of blank space above and below
each block of text (each row) withour adjustng each row individually.

In Word it would be "space above" and "space below".

Thanks
 
S

susanlayton

Thank you for your reply. I am formatting spreadsheets that come to me
already populated. Your solution would work - but would take hours.
The goal is to get uniform spacing above and below the text (text
length of cells varies) without touching every row.
Thanks
 
C

CyberTaz

Hi Susan -

With full appreciation for the suggestions offered the straight fact is that
there is no feature in Excel comparable to those paragraph formatting
options in Word, let alone anything that can be applied to do automatically.
The only conceivable way would be a VBA solution that would evaluate for &
apply the necessary adjustment to row height for each row. It would probably
be more simple to write code to insert 1 or 2 empty rows after the rows
having content - a different approach that might give the same result if
white space between 'records' is the main objective.

It may also be worth considering to move the content into Word as a table.
You could create a table style that would handle the formatting for you.
Other options may be out there but there isn't enough info re your data
files or your requirements to offer anything more specific.
 
S

susanlayton

Hmmm - that (inserting empty rows) is an interesting solution. Tell me
how to write code for that?
And thanks!
 
J

jimdilger

Susan,
Here is a macro that will insert carriage returns
(command-option-return in regular Excel, vbLf in visual basic) before
and after the text of each cell in a selection. If there is already a
CR, it will not be duplicated.
I hope you find it useful.
Jim

Sub LineAboveAndBelow()
'
' select (highlight) a range of cells
' this macro Inserts a CR before and after the text in each of the
selected cells
' thus, the cell height will be enlarged and there will be 1 line above
and below the text
' if the CR already exists, it will not be duplicated

' Macro written 7/24/2006 by (e-mail address removed)
'

Cols = Selection.Columns.Count
Rws = Selection.Rows.Count
FirstCol = ActiveCell.Column
FirstRow = ActiveCell.Row

For c = FirstCol To FirstCol + Cols - 1

For r = FirstRow To FirstRow + Rws - 1

Text = Cells(r, c)
If Mid(Text, 1, 1) <> vbLf Then
Text = vbLf & Text
End If
If Mid(Text, Len(Text), 1) <> vbLf Then
Text = Text & vbLf
End If
Cells(r, c) = Text

Next r
Next c

End Sub
 
J

jimdilger

Susan,

My first macro assumed that the active cell was the upper right cell in
a rectangular selection. This macro allows you to select the cells to
be formatted in any way you wish.

Jim

Sub LineAboveAndBelow2()
'
' select (highlight) a range of cells
' this macro Inserts a CR before and after the text in each of the
selected cells
' thus, the cell height will be enlarged and there will be 1 line above
and below the text
' if the CR already exists, it will not be duplicated

' Macro written 7/24/2006 by James P. Dilger
'
SourceRange = Selection

For Each i In Selection

Text = i.Value
If Mid(Text, 1, 1) <> vbLf Then
Text = vbLf & Text
End If
If Mid(Text, Len(Text), 1) <> vbLf Then
Text = Text & vbLf
End If
i.Value = Text

Next i

End Sub
 
S

susanlayton

Jim-
Thank you so much for thinking out this one. I'll try it. I am a
frequent excel user and can handle more than the basics - but I'm not
so sure I'll be able to do what you suggested. But please know that I
will try and that I very much appreciate your help!

Sue
 
J

jimdilger

Sue,
If you haven't already figured it out...
1. Copy the macro I sent (the stuff in between Sub... and EndSub)
2. In Excel, Tools>Macro>Macros...>
3. Put whatever you like into Macro name,
choose Macros in: Personal Macro Workbook
click Create
4. Paste the clipboard in between the Sub and EndSub statements
5. Excel>Close and Return to microsoft excel

You've saved the macro!
To run it,
1. Select the cells you want to format
2. Tools>Macro>Macros... Click on the macro name, click run.
That's it.

To get fancier, you can assign a keystroke combination to do the job.

Let me know how it goes.
Jim
 
J

JE McGimpsey

Hmmm - that (inserting empty rows) is an interesting solution. Tell me
how to write code for that?
Click to expand...

One way:

Public Sub Insert2Rows()
Const ROW_HEIGHT As Long = 4
Dim nRow As Long
Application.ScreenUpdating = False
nRow = Cells(Rows.Count, 1).End(xlUp).Row
Do While nRow >= 2
With Cells(nRow, 1)
If Not IsEmpty(.Value) Then
.Offset(1).EntireRow.Insert
.Offset(1).RowHeight = ROW_HEIGHT
.EntireRow.Insert
.Offset(-1).RowHeight = ROW_HEIGHT
nRow = nRow - 2
Else
nRow = nRow - 1
End If
End With
Loop
Application.ScreenUpdating = True
End Sub

this macro will insert one row above and one row below every record in
A2:An, and resize the inserted rows to ROW_HEIGHT.
 
S

susanlayton

Jim-
It works!!!!
You have no idea how much time this will save.
Thank you for the solution!
Sue
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Style Gallery View 3
Manual or Automatic Paragraph Spacing? 2
Combining three files when middle file has sections with endnotes 2
Finding the proper style field and the numbered paragraph value within the style 0
Need help modifying code 0
Finding the right style field in VBA 0
Populate a table based on check box status 2
Table Preferred width is changed when pasting 1

Top