 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / July 2006Text spacing within a cell
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???? > 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 [quoted text clipped - 4 lines] > It would be equivalent to "space before" and "space after" a paragraph. > Any ideas???? 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.  Signature------------------------------------------------------------------------ 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 |pjones@kimbanet.com, ICQ11269732, AIM pjonescet ------------------------------------------------------------------------ If it's "fixed", don't "break it"! mailto:pjones@kimbanet.com <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> 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. 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>. 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 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 jimdilger@gmail.com ' 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 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 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 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 > Jim- > Thank you so much for thinking out this one. I'll try it. I am a [quoted text clipped - 3 lines] > > Sue Jim- It works!!!! You have no idea how much time this will save. Thank you for the solution! Sue Sue, Glad to have helped you! Jim > Jim- > It works!!!! > You have no idea how much time this will save. > Thank you for the solution! > Sue 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. 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 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. SignatureRegards |:>) Bob Jones [MVP] Office:Mac >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 [quoted text clipped - 4 lines] > It would be equivalent to "space before" and "space after" a paragraph. > Any ideas???? Hmmm - that (inserting empty rows) is an interesting solution. Tell me how to write code for that? And thanks! I truly wish I could! I have seen it done and once had a macro provided by someone else that did it, and IIRC I've seen a similar [if not exact] version posted on this group some time ago. Alas, I have long since lost the code previously obtained and am not a particularly talented coder. Having planted the seed, however, I'm sure someone more proficient than I will offer something up before long just as generously as the other bits have been. SignatureRegards |:>) Bob Jones [MVP] Office:Mac > Hmmm - that (inserting empty rows) is an interesting solution. Tell me > how to write code for that? > And thanks! > Hmmm - that (inserting empty rows) is an interesting solution. Tell me > how to write code for that? 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. I should have known it would be you, John - I'm gonna burn it to a CD this time 8-)) SignatureRegards |:>) Bob Jones [MVP] Office:Mac >> Hmmm - that (inserting empty rows) is an interesting solution. Tell me >> how to write code for that? [quoted text clipped - 24 lines] > this macro will insert one row above and one row below every record in > A2:An, and resize the inserted rows to ROW_HEIGHT. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.