Need to duplicate rows

[seratne]

Hello,

I have a spreadsheet. Three columns, A B and C. Column A consists o
numbers, how many times something needs to be duplicated. Column B an
C is what needs to be duplicated.

Is there a script or something to do this automatically?

A B C
2 Hi Sir
3 Hi Ma'am

becomes

A B C
2 Hi Sir
2 Hi Sir
3 Hi Ma'am
3 Hi Ma'am
3 Hi Ma'a

 

[Ken Wright]

Try this:-

Sub ExpandRows()

Dim RowNdx As Long
Dim LastRow As Long
Dim n As Long
Dim x As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

On Error Resume Next
For RowNdx = LastRow To 1 Step -1
n = Cells(RowNdx, 1).Value
Rows(RowNdx + 1).Resize(n - 1).EntireRow.Insert Shift:=xlShiftDown

For x = 1 To 3
Cells(RowNdx, x).Resize(n, 1) = Cells(RowNdx, x).Value
Next x

Next RowNdx

End Sub

 

[seratne]

Ken,

Thank you very much for the help. However, I'm completely new to
Microsoft Excel and Macros and Visual Basic. Can you elaborate on what
I need to do to this code and my file to make it work?

Thanks,
Scott

 

[JE McGimpsey]

See David McRitchie's "Getting Started with Macros"

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[seratne]

While the Getting started guide was helpful, I'm afraid i'm not quite u
to learning a scripting language. Can someone break down th
macro(comment it) and briefly explain what I need to do to m
spreadsheet to accomplish my repeating task?

Thanks,
Scott

 

[seratne]

Can someone explain this to me and how to use it?

Sub ExpandRows()

Dim RowNdx As Long
Dim LastRow As Long
Dim n As Long
Dim x As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

On Error Resume Next
For RowNdx = LastRow To 1 Step -1
n = Cells(RowNdx, 1).Value
Rows(RowNdx + 1).Resize(n - 1).EntireRow.Insert Shift:=xlShiftDown

For x = 1 To 3
Cells(RowNdx, x).Resize(n, 1) = Cells(RowNdx, x).Value
Next x

Next RowNdx

End Su

 

[David McRitchie]

You asked about explaining the code and about how to implement.
and thanked the responder for showing you my getstarted.htm web page.

First things first. If you installed the macro properly it should work.
- Did you install the macro in the same workbook, or another workbook
- Did you invoke the macro for example form Alt+F8
- What happened instead of working.

Do you see ExpandRows as a macro when you hit Alt+F8
if you do then it was installed. If you see it does it have an
exclamation point before the macro name. If not it was installed
in the same workbook, and if it does it was installed in another
open workbook. Either way you can invoke the macro from
the Alt+F8 (Macro dialog).

The macro supplied would be installed and really wouldn't make any
difference whether you understand it or not. But once you change it
you become a programmer.

I think your complaint is that the macro is not working for you so
asking how to change it is jumping the gun. But if it works and
you want changes tell us what you want changed. I think I might
change the first column to a 1 afterwards so that rerurnning the
macro would not result in a huge increase in the number of rows..