Insert row when column data changes and sort groups

I need to insert a blank row where data the data changes in column A

Example

A B
Jan 213
Jan 345
Jan 113
Feb 534
Feb 322
Feb 456
Mar
Mar
Mar

I then need to sort between each blank row column B
A B
Jan 113
Jan 213
Jan 345

Feb 322
Feb 456
Feb 534

Thanks

The code below will help you to insert a row after each change of data.

Please Login or Register  to view this content.

Am working on the sort part. Am a lil stuck. Will update shortly.

This is the code complete with the sorting - Try it out and let me know if you face any issues.

Please Login or Register  to view this content.

Thanks for helping me out on this.

The first sub worked fine but the second didn't sort each group in column B.

You dont need to use the first sub. The 2nd sub contains both the line insertion and the sorting. Try it out and let me know.

Yes i tried the second one on its own

it performs the insert row but doesnt sort B

"runtime error9
subscript out of range"

ok let me look at it again....

It works fine on my copy of the workbook. One question - is your sheet named Sheet1? If its something else, you have the option of either changing the sheet to "Sheet1" or change this line of code to reflect the new sheet name.

Please Login or Register  to view this content.

My Bad

the code does work correctly in the example that i gave you however the data i am using is slightly different.

i.e it has more than two columns although B is the one i would like to sort after the insert of a blank row.

When i get back later i will post a more accurate example as it may require an amended code.

Cheers

Ok so when i tried this code with a sheet that only had data in column A and B it worked fine so Im guessing the fact the my data goes to column O is causing the issue. Sorry I'm very new to vba i'll try to be more exact


Once the above is sorted I would also like to take it a step further.

I want to be able to create an order for each section(blank row) (e.g column C) so i can filter out the top 1, 2 or 3.


A - B - C

Jan 113 - 1
Jan 213 - 2
Jan 345 - 3
Jan 566 - 4
Jan 766 - 5

Feb 322 - 1
Feb 456 - 2
Feb 534 - 3
Feb 786 - 4
Feb 987 - 5

Thanks,

Beat

Ok. Attach your file and we can work through it. Remember, in future, if you have any questions, post a sample that contains dummy data but is exactly the same in format and structure as your original data. This will make it easier to duplicate the code or formula in your original file without much waste of time.

Ok I got this working when i copied and pasted the values only into a new worksheet.

It may have been because column B was a formula average of D to O. (see example)

I will be more exact in the future and the examples attached are identical to what im working with.

See example 2 for the example of the sorting in column D ( it doesn't have to be bold btw)

Thanks

Its better if you attach the excel file instead of the jpg. So is the issue resolved or are you still facing problems?

Still having probs with the sorting

Attachment 127542

im pretty sure its the average calculation in B as it works fine when copied as a value only to another "sheet1"

thanks

I am not able to open the attachment. Go to new post and then click go advanced and u will be able to attach.

example for forum.xlsx

Can you open this?

Yes it opened. Let me look into it in more detail and i will revert.

Much appreciated

Use this code - I have edited it a bit and it works -

Please Login or Register  to view this content.

Thanks a lot this works great.


Ive worked out how to do the below manually using autofill and filtering but it would be awesome if this was tied into the code as I'm working with a massive amount of data

I want to be able to create an order for each section (see column C)

A - B - C

Jan 113 - 1
Jan 213 - 2
Jan 345 - 3
Jan 566 - 4
Jan 766 - 5

Feb 322 - 1
Feb 456 - 2
Feb 534 - 3
Feb 786 - 4
Feb 987 - 5

Thanks,

Beat

Your revised code will now be -

Please Login or Register  to view this content.