Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 804
  • Last Modified:

Modify the spacing within cells in Excel

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
Steve_Brady
Asked:
Steve_Brady
3 Solutions
 
Rob HensonIT & Database AssistantCommented:
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
 
kgerbChief EngineerCommented:
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
 
pgorodCommented:
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
 
Steve_BradyAuthor Commented:
Thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now