Adding a blank line into different sized rows

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

1techgirl

Thread Starter
Joined
Dec 10, 2004
Messages
167
Good morning

Excel 2003

I would love to know how to add a blank space after some text in a cell. I have typed up a document of varying sized rows of text and my boss wants each cell to have a blank space underneath the bottom row of text. Short of going into each cell and pressing Alt + Enter, which is going to take me ages as there are hundreds of rows, does anyone know how to solve this with a quick formatting option?

Highlighting the whole document and double clicking the double arrows when over the rows only heightens the longest cell in the whole document - which is no good because the cells are of different heights according to how much text is in each one.

I am stumped - please help and save me loads of time today!

Many thanks

1techgirl
 
Joined
Sep 18, 2008
Messages
22
Two quick ideas to try.

1. go to cell properties and select alignment. set to left, centre or right in horizontal as you like but set centre in vertical alignment. Format > row> height and increase the height of all cess at the same time to create the white space.

2. Assume that your original text is in a1. Format the cell with vertical alignment, centre. (right click, cell properties).
Then select any empty cell on the same row (b1) and edit it. press ctl+enter, then enter to create the space. In the next empty cell (c1) use the concatenate formula =concatenate(a4,a5) (or type a4&a5: quick way). Text wizard is useful if your not sure what I mean.

The results should then have the format you need. Copy the formula down for each row and then copy the complete col C and paste special, values over your original in col A.

Hope this works for you. I have spent hundreds of hours cleaning text for upload to new computers so have develpoed some sad but useful skills over the years. Regards - Nic
 
Joined
Jul 1, 2005
Messages
8,546
"boss wants each cell to have a blank space underneath the bottom row of text"

Do you actually want ALT+Enter in the cells, or just the row height adjusted (for visual purposes) without having to do each row manually?

If the latter, then a very simple macro:

Sub test1()
For Each Cell In Selection
Cell.RowHeight = Cell.RowHeight + 12.75
Next Cell
End Sub


Post back if you need help with installing/running macros.
 
Joined
Jul 1, 2005
Messages
8,546
& here's the other way, if you like.

A1 of sheet New in the attached contains a carriage return.

Select A1:A2 on Sheet1. Hit ALT+F8 to run the Macro dialog. Click on "test2", then click Run.
 

Attachments

Joined
Oct 20, 2004
Messages
7,837
If you don't have too many columns, this would work. I will assume you just have data in A, B, and C. In D1 enter Alt + Enter. In E1 enter =A1&$D$1.
Drag across F and G (to match up with B and C) and then drag down.
You will now see the data from, say, A1 with a little box (indicating a break) after it.
Go to D1 and click on the format painter. Highlight all the cells in E, F, and G.
They will expand to fit and the boxes will disappear.
Now copy those cells and Paste Special, values. If you click in one, you will find the data followed by a blank line.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top