Macro in Excel to repeat entire row n number of times

Hi,

I need a macro in EXCEL to repeat entire rows n number of times where n is in the row to be repeated. I have tried using answers already here but they produce either 1 too many rows, or do not copy the entire row. Ie:

Col1 Col2 Col3 Col4 Col5
ABC ABC ABC ABC 2
DEF DEF DEF DEF 3

Macro produces
ABC ABC ABC ABC 2
ABC ABC ABC ABC 2
DEF DEF DEF DEF 3
DEF DEF DEF DEF 3
DEF DEF DEF DEF 3

Thank you so much for anyone's help, really appreciated.

Tom

This is modified from some ancient code of mine, so it's a bit inelegant, but it should work.

Please Login or Register  to view this content.

Thanks for your help so far! But I can't get it to work :-(

I get a runtime error '13' - Type mismatch

Related to line For j = 2 To Reps

Is every value in column D an integer? Do you maybe have a header row that isn't a number? If so, just change the i = 1 to whatever row has the first integer value.