Solved How to create line spacing with Excel?

Dell - inspiron 17.3" laptop - 4gb memor...
July 19, 2014 at 08:32:55
Specs: Windows 8.1, AMD A8/4.0Gb
I have a long series of numbers/letters such as M09-111, M12-999, etc. many of which repeat. These are in a list that is arranged in descending order, I have tried to use functions to place a line between different numbers but have been unable to do that. What I need looks like this:

M09-111
M09-111
M09-111

M12-999

M14-345
M14-345

Thank you.
Brian W


See More: How to create line spacing with Excel?

Report •


#1
July 20, 2014 at 08:15:31
Since Excel uses the tables and cells layout, there are no line-spacing controls because that does not apply to such a layout.

If you want to leave extra blank space between one set of numbers and another, just leave a row of cells empty between the two sets like this:

M09-111
M09-111
M09-111
<leave empty row of cells across here>
M12-999
M14-345
<leave empty row of cells across here>
M14-345

message edited by phil22


Report •

#2
July 21, 2014 at 07:53:23
✔ Best Answer
Try this code.

Note: The code assumes that your data is in Column A.

If the data is in some other column, just change all the "A"s to match the correct column letter.

Sub InsertRowAtChange()
'Determine last cell with data in Column A
   lastRw = Cells(Rows.Count, "A").End(xlUp).Row
'Loop through cells in reverse order, inserting a Row _
'at each change in data
    For rw = lastRw To 2 Step -1
      If Cells(rw, "A") <> Cells(rw - 1, "A") Then
         Cells(rw, "A").EntireRow.Insert
      End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
July 21, 2014 at 11:11:14
phill22,

Your response is valid if the user is entering the data manually, but if he already has a "long series of numbers/letters" in a spreadsheet, manually inserting lines at every change could be a long (and boring) process.

Since, as you mentioned, "there are no line-spacing controls" in Excel, a macro, as shown in my previous response, can be used to do the job quite easily.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 21, 2014 at 14:26:39
DerbyDad03,

Thank you for your suggestion. Unfortunately, I did the line spacing manually before I received your post.

Thank you again.
Brian W


Report •

Ask Question