Solved

Modify the spacing within cells in Excel

Posted on 2011-09-27
4
778 Views
Last Modified: 2012-05-12
Hello,

Is there any way to modify the spacing within cells in Excel (2010)?  I have never found any settings to affect this within the normal Excel controls.  However, I am wondering if it can be done through some kind of programming such as VBA.

Specifically, I am referring to the spacing settings which are managed by the Paragraph box in MS Word.  
 aThe settings there are grouped into three sections:

• General alignment -- this section is a non-issue because Excel's alignment settings include all the options found in Word and even more

•  Indentation -- although Excel does have indentation settings which affect the left margin of text, I have never found anything to alter the right margin or create first-line indentation or hanging.

• Spacing -- as far as I know, there are no options to change the inter-line settings in Excel.  Changing the font type and/or size affects the inter-line spacing slightly but customizing the spacing is not possible.

I have very little understanding of VBA but does it have the capability to affect the formatting of text within cells in the ways mentioned above?  As stated initially, I am mostly interested in being able to make changes in the third category:  inter-line spacing.

Thanks
0
Comment
Question by:Steve_Brady
4 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 300 total points
ID: 36710922
Steve,

I have found that Excel is not really designed particularly well to handle text publishing as well as MS Word would.

I have had numerous occasions where a report combines numerical data with text but need it formatted better than available in Excel - bullets, indents etc.

I have got round it in the past by embedding a Word document within an Excel sheet or embedding an Excel document within a Word document; depending on which there is the most of, words or numbers.

Alternatively, you can use a table within Word to get column and row alignment of numbers but there isn't the mathematical functions like there is in Excel. You can create the table of data in Excel and then copy and paste as a table, use Paste as RTF if you want the formatting.

Thanks
Rob H
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 100 total points
ID: 36711532
Steve_Brady,
I believe the best way to change the inter-line spacing is to add text to different rows and then adjust the row height.  Sorry, I know that's not the answer your were looking for.

http://office.microsoft.com/en-us/excel-help/change-line-spacing-in-excel-HA001034624.aspx

Kyle
0
 
LVL 4

Assisted Solution

by:pgorod
pgorod earned 100 total points
ID: 36923579
As far as the VBA you asked for is concerned, you can handle that sort of thing through Range's Interior object, like this:

Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 3

Full options here: http://msdn.microsoft.com/en-us/library/ff839659.aspx

To  format only some characters within a cell, use the Characters() collection. See an example here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=743

However, you won't be able to do much regarding paragraph formatting. Only Font formatting options are available...
0
 

Author Closing Comment

by:Steve_Brady
ID: 36951990
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 76
Excel Formula 5 44
Auto Populate Day Month  2 digit Date 4 19
Alphabetical Order for Letters 2 21
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question