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-111M12-999
M14-345
M14-345Thank you.
Brian W
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-345message edited by phil22
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03
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.
DerbyDad03, Thank you for your suggestion. Unfortunately, I did the line spacing manually before I received your post.
Thank you again.
Brian W
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |