Insert blank rows based on a cell value

[warrenr6]

Hey guys,

i was wondering if i could ask for some help,

i am in the process of building a motorbike parts website and i have comprised a list of all bikes of all makes all models and all years, it has over 20,000 rows of data so would be a bit tedious to do manually haha

here is a sample of the data:

Honda CRF 150 R----- 7----- KE03A------ JH2KE03A-7K000001------ JH2KE03A-7K099999------ 2007----------- 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 R----- 8----- KE03A------ JH2KE03A-8K100001------ JH2KE03A-8K199999------ 2008----------- 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 R----- 9----- KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2009-2011------ 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 R----- C----- KE03A------ JH2KE03A-CK300001------ JH2KE03A-DK499999------ 2012----------- 1------ 24 BHP, 18 kw------
Honda CRF 150 R----- D----- KE03A------ JH2KE03A-DK400001------ JH2KE03A-DK499999------ 2013----------- 1------ 24 BHP, 18 kw------
Honda CRF 150 R----- E----- KE03A------ JH2KE03A-EK500001------ JH2KE03A-DK499999------ 2014-2015------ 1------ 24 BHP, 18 kw------
Honda CRF 150 RB----- 7----- KE03B------ JH2KE03B-7K000001------ JH2KE03B-7K099999------ 2007----------- 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 RB----- 8----- KE03B------ JH2KE03B-8K100001------ JH2KE03B-8K199999------ 2008----------- 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 RB----- 9----- KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2009-2011------ 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 RB----- C----- KE03A------ JH2KE03A-CK300001------ JH2KE03A-DK499999------ 2012----------- 1------ 24 BHP, 18 kw------
Honda CRF 150 RB----- D----- KE03A------ JH2KE03A-DK400001------ JH2KE03A-DK499999------ 2013----------- 1------ 24 BHP, 18 kw------
Honda CRF 150 RB----- E----- KE03A------ JH2KE03A-EK500001------ JH2KE03A-DK499999------ 2014-2015------ 1------ 24 BHP, 18 kw------

My issue comes from the year column,

the first to rows are ok, but its any row that contains multiple years i.e the third row down is 2009-2011,

what i need to be able to do is to insert two empty rows below that row, as the cell that "2009-2011" was in will now have to contain "2009" then the two cells below need to be "2010" and "2011" respectively, and in an ideal world, fills in the data identically to in the other columns ( i hope that makes sense lol)


So basically this row:


Honda CRF 150 RB------ 9------ KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2009-2011------ 1------ 22,6 BHP, 16,6 kw------



Becomes:

Honda CRF 150 RB------ 9------ KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2009------ 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 RB------ 9------ KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2010------ 1------ 22,6 BHP, 16,6 kw------
Honda CRF 150 RB------ 9------ KE03A------ JH2KE03A-9K200001------ JH2KE03A-9K299999------ 2011------ 1------ 22,6 BHP, 16,6 kw------



I dont know if this is possible by a formula (probably not), i have no experience with macros or vba but i am willing to learn lol

thanks for any help in advance,

Warren.

ps. I have attached an excel version of the sample if it helps

[MarvinP]

Hi Warren,

Run this code while looking at your sheet (make a backup first), and see if it does what you are asking for.

Please Login or Register  to view this content.

Fill In Row data across dash years.xlsm

[rollis13]

I'm late ... well this was what i managed (to be copied in the sheets' vba module):

Please Login or Register  to view this content.

[warrenr6]

Hi guys,

thanks for the replys,

how do i do this i have never done anything with codes or vba`s,

sorry completely new to this way of doing things,

regards,

warren.

[rollis13]

Always use a copy of your file for testing.
Click on the sheets' Tab and choose View Code and then paste my code in the right panel.
Or press Alt+F11 to go to the VBA panel. DoubleClick on your sheets' name on the left and then paste my code in the right panel.
Go back to your sheet and press Alt+F8 to choose a macro to be run.

MarvinP has attached a copy of his file.

[warrenr6]

Hey,

i tried it on the small sample range and it worked perfectly, however when i tried to do it to the full file it said script out of range?

would you like me to attach the full file?

would it be because a few odd year cells are empty?

regards,

warren

[rollis13]

Yes, it's because the cell is empty.
Change this line of code:

Please Login or Register  to view this content.

[warrenr6]

Hey

thanks for that,

i changed what you said,

when i ran the code, it added 796 new rows but an error came up that said "Type Mismatch"

Any ideas? Am i doing something wrong?

regards,

Warren.

[rollis13]

If you don't state where the Debug stops and the value of the variables at that moment it's hard to realize what triggers the error.
So, now it's better if you attach the full file as you suggested in your post #6.

[warrenr6]

Hi rollis13,

sorry for that,

im not 100% where it stopped, i thought maybe i had entered something wrong, i will attach the file for you to see here, i really do appreciate all your help,

thanks,

warren

[warrenr6]

i will have to do it in a few parts as the file exceeds what is allowed to be uploaded

[warrenr6]

file 1 and 2

[warrenr6]

file 3

thanks,

regards,

warren.

[rollis13]

Yes, you have some other cells with offending data. To help finding them you can use the macro "Validate_Years" and take note of the offending cells.
Then you can search and modify them; use key F5 and indicate the coordinates of the cell in the field "Reference" ex. F1855 then correct the data.
Once finished you may launch the macro "Duplicate_Years".
As before, copy all the code in the sheets' vba module.

Please Login or Register  to view this content.

[warrenr6]

Hi Rollis13,

that looks to have worked perfectly, i really appreciate your help,

best regards,

Warren.

[rollis13]

Glad having been of some help