How to insert a row and fill in missing data with macro

**Catherine7** · 03-07-2010, 05:43 PM

Hello forum!

I am working with a really large excel file and I could really need som help with writing a code for a macro! My file looks like that:

Row B C D
1 1 45 F
2 3 33 M
3 5 37 M
4 9 87 F
5 15 67 F

where column B = patient's number, C=age, D=*** etc
What I want to do is insert a blank row for every patient missing (like number 2, 4, 6, 7 etc) so it looks like this:

Row B C D
1 1 45 F
2 2
3 3 33 M
4 4
5 5 37 M

I would like to fill in only Column B with the missing numbers but the other columns will be left blank.

If you could provide me with the right code for a macro I would be really, really greatful.
Thank you!!!

Catherine7

**martindwilson** · 03-07-2010, 06:25 PM

non macro
in the first cell at end of your data with gaps
say its column a and last used cell is a1000
in a1001 put
=IF(COUNTIF($A$2:$A$1000,ROWS($A$1:A1))=0,ROWS($A$1:A1),"")
drag down untill last number catered for
copy past special values and sort

**Catherine7** · 03-07-2010, 07:39 PM

Thank you Martin for your quick reply!
I tried that and it doesn't work! Is there any other way to insert rows and fill missing entries in a series?
Many, many thanks

**jaslake** · 03-07-2010, 07:48 PM

Hi Catherine7

If you would post a sample workbook that shows what you have (before) on one sheet and a sheet of what you wish it to be (after), I'll be glad to look at the issue with you.

John