Conditionally insert rows

[CakzPrimz]

Dear MrExcel,

I need to insert rows based on certain numbers and I need your kind help and assistant to help me out.
I have 2 columns, A & B
A is containing value (string)
B is containing value (number)

If the value in column B = 1 then no inserting rows
But if the value in column B <> 1, let's say 2 then it will insert 1 (one) row
And if the value in column B <> 1, let's say 5 then it will insert 4 (four) rows

Before:
Col A Col B
Row 1 - Apple 1 (no inserting rows)
Row 2 - Sugar 2 (insert 1 row)
Row 3 - Cookies 1

After
Col A Col B
Row1 -Apple 1 (no inserting rows)
Row2 - Sugar 2 (insert 1 row)
Row3 - Sugar 2
Row4 - Cookies 1

Thanks in advance.

 

[Paul Ked]

Try:

Sub Test()
    Dim rw As Long, i As Long
    rw = 1
    Do Until Cells(rw, 1) = ""
        If Cells(rw, 2) > 1 Then
            For i = 1 To Cells(rw, 2) - 1
                Range("A" & rw & ":B" & rw).Copy
                Range("A" & rw).Insert Shift:=xlDown
                rw = rw + 1
            Next
        End If
        rw = rw + 1
    Loop
End Sub

 

[Trebor76]

Hi CakzPrimz,

Try this:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False

    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = lngLastRow To 1 Step -1
        If Val(Range("B" & lngMyRow)) >= 2 Then
            For i = 1 To Val(Range("B" & lngMyRow)) - 1
                Rows(lngMyRow).EntireRow.Insert
                Range("A" & lngMyRow).Value = Range("A" & lngMyRow + 1).Value
                Range("B" & lngMyRow).Value = Range("B" & lngMyRow + 1).Value
            Next i
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[CakzPrimz]

Try:

Sub Test()
    Dim rw As Long, i As Long
    rw = 1
    Do Until Cells(rw, 1) = ""
        If Cells(rw, 2) > 1 Then
            For i = 1 To Cells(rw, 2) - 1
                Range("A" & rw & ":B" & rw).Copy
                Range("A" & rw).Insert Shift:=xlDown
                rw = rw + 1
            Next
        End If
        rw = rw + 1
    Loop
End Sub

Dear Paul Ked,
Your code works like a charm!
The only problem, it won't run if the data is not starting from the first row.
Since, I have the header in row # 2
And if you could not modified it, still I go along with you.

Really appreciate it.
Thanks for your kind assistant and helping hands.

 

[Paul Ked]

Change rw = 1 to rw = 2 ​or the first row your data is on!

 

[CakzPrimz]

Hi CakzPrimz,

Try this:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False

    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = lngLastRow To 1 Step -1
        If Val(Range("B" & lngMyRow)) >= 2 Then
            For i = 1 To Val(Range("B" & lngMyRow)) - 1
                Rows(lngMyRow).EntireRow.Insert
                Range("A" & lngMyRow).Value = Range("A" & lngMyRow + 1).Value
                Range("B" & lngMyRow).Value = Range("B" & lngMyRow + 1).Value
            Next i
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Hi Trebor76,

Thanks for your help, I've tried the code and it works, even if I have header in row # 1
Problem solved.
I am speechless, but I do believe that all of you in MrExcel are guardian angels, helping each sincerely.

Dear Trebor76 and Paul Ked, and others in this incredible forum.
Thanks for your everything.
You are the best !

Best regards,
Cakz Primz
Indonesia

 

[Paul Ked]

You're welcome, just remember how important the accuracy of the question is...

Row 1 - Apple 1 (no inserting rows)
Row 2 - Sugar 2 (insert 1 row)
Row 3 - Cookies 1

but your data didn't start at row 1

Glad to have helped

 

[CakzPrimz]

Dear Paul Ked,

I apologize for not clearly describe. And I changed the code as your instruction, and it works.
If I apply the code for another works someday, but I want to copy the entire row, from column A until the last column of data. How to modify the code?

Again, thanks so much.