Insert row when a row in the sequence is missing

Hello,

I have a spreadsheet that may contain many rows. In one column, ex. Column A, there is a number sequence from 1 to 13 and then it starts with 1 again. The problem is that when i import the data to the spreadsheet there might be a row here and there that is missing, due to lack of data for that partickular row in the sequence. I would need a macro that inserts a blank row where ever there is a row missing.

Example:
Column A Column B Column C
1 data data
2 data data
4 data data
5 data data
1 data data
2 data data
3 data data
5 data data
1 data data
...

In this case i would like the macro to insert a row 3 and then a row 4.

I have tried some solutions but they seem only to work on a sequence of continuos numbers. I run into trouble wit a sequence that runs up to 13 and then starts over with one again.

I would be grateful for any input i can get.

Cheers,
Michael

Perhaps a macro like this?

Please Login or Register  to view this content.

This macro loops from 1 to 5 then it starts all over again as in your example.

there is a number sequence from 1 to 13 and then it starts with 1 again.

????

Alf

Wow!

I do beleive that nailed it. Great!
Thanx a lot Alf!

Glad to be of help and since this seems to solve your problem could you please mark the thread "Solved"

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Alf

Ps If you like to give a rating to my answer click on the small star bottom left in my post.

Found 1 problem with this macro. If the first value (cell A1) is not 1 then the macro does not work properly so I've updated the macro with a test for the value in cell A1 and correcting if this value is not 1.

Please Login or Register  to view this content.

Alf

I really thought the problem was solved but I finally discovered one irritating point. As I described above I had a sequence of rows 1 through 5. And everything is fine unless the last row, a number 5, is missing. For example if I have the sequence: 1,2,4,1,2,3,5,1,2,3,4. I then run the macro and all missing rows will be inserted except the last one.

Does anyone have a soultion?

It would be greatly appreciated.
Cheers,
Michael

Ok

Here we go again. This macro will always give you a range of values from 1 to 5 in column A.

Please Login or Register  to view this content.

So if I ever come to Uppsala you owe me a Caffe Latte in the meantime click om my star (bottom left) and give a rating to my answer!

Alf

Thanx Alf! You've been a great help. And if you get to Uppsala I'd buy you several Lattes.

A new problem turned up though. If I try to use this macro for column D instead of column A the problem with the last lines persists. I naively thought I could change all Column A references in the macro to Column D instead. But when I do that, and if the last row (row 5) is one of the missing rows, it does not show up. I also discovered that if there is no info in column A when I run the macro, this last number 5 turnes up in cell A3.
I tried to fix this but I do not see the where the problem lies.

Many Thanx!
Michael

Could you upload a small sample file with the new layout i.e. the way you get the data and the way you wish it "sorted".

Alf

Ps Thanks for rep so I'll forget about Latte but thanks anyhow.

Have written a small word document with comments for the macro.

Could perhaps be of help to you.

Alf

Thanx Alf!
I did finally solve it.
Apart from changing the "A1" to "D1" I also discovered that i had to change the following:
from "j = Cells(Rows.Count, 1).End(xlUp).Row" to "j = Cells(Rows.Count, 4).End(xlUp).Row"
and so on for the other lines with Cells.
I have tested the final macro many times now and it seems to work splendidly.
Many thanks for the explanations in "Macro_commnts". You really have been a great help.


Cheers
/Michael

Bara kul att kunna vara till hjälp.

Alf