Hello All..!!
I hate to say it, but "I'm new to VBA". I've always used Excel and loved it, but my method was always the method of many many mouse clicks.
Lately i've been working with sheets with more than 1500 rows, that refer to more than 300 worksheets.
I'm finding lots of useful information on the internet in forums here and there. My problem is i'm not able to join the various things I’ve learned.
What I need to do is copy multiple rows based on a numerical value from a single column.
Example:
Quantity A B C ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
to this…
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
The code I’ve been trying to work with inserts blank rows. Where I need it to copy the previous row based on the value in the first column.
Sub AddMultitipleRows2()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
' Based on the value in column b, where Quantity is in the
' example above.
For iRow = LastRow To FirstRow Step -1
With .Cells(iRow, "b")
If IsNumeric(.Value) Then
If .Value > 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
If .Value = 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
End If
End With
Next iRow
End With
End Sub
thank you to anyone that could take the time to help.
Dogbone...
I hate to say it, but "I'm new to VBA". I've always used Excel and loved it, but my method was always the method of many many mouse clicks.
Lately i've been working with sheets with more than 1500 rows, that refer to more than 300 worksheets.
I'm finding lots of useful information on the internet in forums here and there. My problem is i'm not able to join the various things I’ve learned.
What I need to do is copy multiple rows based on a numerical value from a single column.
Example:
Quantity A B C ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
to this…
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
The code I’ve been trying to work with inserts blank rows. Where I need it to copy the previous row based on the value in the first column.
Sub AddMultitipleRows2()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
' Based on the value in column b, where Quantity is in the
' example above.
For iRow = LastRow To FirstRow Step -1
With .Cells(iRow, "b")
If IsNumeric(.Value) Then
If .Value > 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
If .Value = 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
End If
End With
Next iRow
End With
End Sub
thank you to anyone that could take the time to help.
Dogbone...