ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Insert Rows Macro (https://www.excelbanter.com/excel-discussion-misc-queries/164086-insert-rows-macro.html)

A.S.
Insert Rows Macro
 
Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

Dave Peterson
Insert Rows Macro
 
This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson

A.S.
Insert Rows Macro
 
If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson

excelent
Insert Rows Macro
 
Sub tst()
rw = Cells(1000, 3).End(xlUp).Row
For r = rw To 22 Step -1
Cells(r, 3).Select: n = ActiveCell.Offset(0, 2).Value
If n 1 Then
Selection.Offset(1, 0).Resize(n, 1).EntireRow.Insert
ActiveCell.Offset(1, 0).Resize(n, 1) = ActiveCell
ActiveCell.Offset(1, 1).Resize(n, 1) = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 2).Resize(n, 1) = 1
End If
Next
End Sub


"A.S." skrev:

If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson

Dave Peterson
Insert Rows Macro
 
The code was written to process data in columns A:C in rows 2 to whatever.

Option Explicit
' If it is 1 or 0, it should leave as-is. However, I tried the macro and
' doesn't appear to do anything. Am I doing something wrong? Do I need to
' designate somewhere where to start looking? In my sheet would start on row
' 22. The Product is on C22, the quantity is on D22, and the code is on E22.
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 22 'headers in row 1
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "d")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 1 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "c").Value
.Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1
.Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "e").Value
End If
Next iRow
End With
End Sub

A.S. wrote:

If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson


--

Dave Peterson

A.S.
Insert Rows Macro
 
I moved everything over to columns A:C but still does not do anything? Any
thoughts to why?

"Dave Peterson" wrote:

The code was written to process data in columns A:C in rows 2 to whatever.

Option Explicit
' If it is 1 or 0, it should leave as-is. However, I tried the macro and
' doesn't appear to do anything. Am I doing something wrong? Do I need to
' designate somewhere where to start looking? In my sheet would start on row
' 22. The Product is on C22, the quantity is on D22, and the code is on E22.
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 22 'headers in row 1
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "d")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 1 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "c").Value
.Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1
.Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "e").Value
End If
Next iRow
End With
End Sub

A.S. wrote:

If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Insert Rows Macro
 
I changed the macro and reposted according to your requirements.

If you used the first version, it looks in A2:C###.



A.S. wrote:

I moved everything over to columns A:C but still does not do anything? Any
thoughts to why?

"Dave Peterson" wrote:

The code was written to process data in columns A:C in rows 2 to whatever.

Option Explicit
' If it is 1 or 0, it should leave as-is. However, I tried the macro and
' doesn't appear to do anything. Am I doing something wrong? Do I need to
' designate somewhere where to start looking? In my sheet would start on row
' 22. The Product is on C22, the quantity is on D22, and the code is on E22.
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 22 'headers in row 1
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "d")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 1 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "c").Value
.Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1
.Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "e").Value
End If
Next iRow
End With
End Sub

A.S. wrote:

If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

A.S.
Insert Rows Macro
 
Thanks Dave, this worked great!

"Dave Peterson" wrote:

I changed the macro and reposted according to your requirements.

If you used the first version, it looks in A2:C###.



A.S. wrote:

I moved everything over to columns A:C but still does not do anything? Any
thoughts to why?

"Dave Peterson" wrote:

The code was written to process data in columns A:C in rows 2 to whatever.

Option Explicit
' If it is 1 or 0, it should leave as-is. However, I tried the macro and
' doesn't appear to do anything. Am I doing something wrong? Do I need to
' designate somewhere where to start looking? In my sheet would start on row
' 22. The Product is on C22, the quantity is on D22, and the code is on E22.
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 22 'headers in row 1
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "d")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 1 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "c").Value
.Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1
.Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _
= .Cells(iRow, "e").Value
End If
Next iRow
End With
End Sub

A.S. wrote:

If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to
designate somewhere where to start looking? In my sheet would start on row
22. The Product is on C22, the quantity is on D22, and the code is on E22.

"Dave Peterson" wrote:

This may do what you describe, but I'm not sure it does what you want.

What happens when one of the quantities in column C is 1?

Option Explicit
Sub testme()

Dim iRow As Long
Dim HowMany As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowMany = 0
With .Cells(iRow, "C")
If IsNumeric(.Value) Then
HowMany = .Value
End If
End With
If HowMany 0 Then
.Rows(iRow).Offset(1, 0).Resize(HowMany).Insert
.Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1
End If
Next iRow
End With
End Sub


A.S. wrote:

Hello,
I couldn't find a macro that does this, so if anyone can point me in the
right direction I would appreciate it:

Sample Data:
Product Code Qty
ABCD AXD 2
DJFL DFY 3

Basically I want the macro to look at the quantity and recognize 2 and then
insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number
1 entered in the Qty for all the rows. So basically, data would then look
like:

Product Code Qty
ABCD AXD 2
ABCD AXD 1
ABCD AXD 1
DJFL DFY 3
DJFL DFY 1
DJFL DFY 1
DJFL DFY 1

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 08:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com