SOLVED

Need to add blank lines

Copper Contributor

I am using Excel out of Office 360.

I need to sort a table with dates and account numbers. I need to sort by the account numbers, then add a blank line between the different group of account numbers using a macro.

Is that possible?

Sample data below.

DateAccount #
12/8/20215500030
12/9/20218400084
12/10/20218400084
12/15/20215500061
12/16/20215800840
12/17/20215500061
12/17/20218400084
12/21/20215500061
12/22/20215500067
12/23/20215500061
12/23/20215500067
12/23/20218400084
12/23/20215500084
12/24/20215500061
12/27/20215500083
12/27/20215500067
12/28/20215500083
12/30/20215500083
12/31/20215500083
1/3/20225800815
1/3/20228400067
1/3/20225800815
1/3/20225800815
1/3/20225800815
1/3/20225800815
1/3/20225800815
1/3/20225800815
1/5/20225500061
1/6/20225500061
14 Replies

@LarryHarrison1988 

If you click the button in cell E1 in the attached file, is this what you want to do?

@OliverScheurich 

When I clicked on the button, it sorted the table and added lines between the different numbers, then also copied the information into several cells to the right.

I tried it again and it worked like it is supposed to.
If I want to use the macro on a larger table, what do I need to edit?

@LarryHarrison1988 

Key:=Range("B2:B46" _

.SetRange Range("A1:B46")

For i = 2 To 100

 

Edit the ranges and the upper value in the for statement of the macro according to your requirements.

 

I tried the attached example with 2000 rows and it works as intended in my spreadsheet after i edited the ranges and the upper value of the macro.

How can I submit my file for a better example?

I have done the sort and added your text below "End With"

Do I need to edit any of that?

@LarryHarrison1988 

Click Reply

Then click

Open full text editor

Then click

browse

to select the file you want to attach.

 

@OliverScheurich 

I don't have an option to browse for a file.

I have 9 columns of data and variable rows. I can do the sort, but I just need to insert blank rows between the groups.

My video showing how to do this with Power Query might help
https://youtu.be/Wq8Iuuw7DFE

@LarryHarrison1988 

Maybe like in the attached file if you click the button in cell K1. The data is already sorted by column I and the macro executes the insertion of blank rows according to the groups in column I.

That will work.
I need to insert a line based on my column "E"
best response confirmed by LarryHarrison1988 (Copper Contributor)
Solution

@LarryHarrison1988 

Maybe like in the attached file if you click the button in cell K1. The data is already sorted by column E and the macro executes the insertion of blank rows according to the groups in column E.

@OliverScheurich 

I created a macro to sort the data.

It ended with the following.

      End With
End Sub

 

If I paste the macro from your file, what should I do to make it work?

 

I was able to edit the macro and make it work for my document.
Thanks for your help.
1 best response

Accepted Solutions
best response confirmed by LarryHarrison1988 (Copper Contributor)
Solution

@LarryHarrison1988 

Maybe like in the attached file if you click the button in cell K1. The data is already sorted by column E and the macro executes the insertion of blank rows according to the groups in column E.

View solution in original post