Conditionally insert rows

CakzPrimz

Board Regular
Joined
Oct 6, 2017
Messages
57
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try:

Code:
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
 
Upvote 0
Hi CakzPrimz,

Try this:

Code:
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
 
Upvote 0
Try:

Code:
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.
 
Upvote 0
Change rw = 1 to rw = 2 ​or the first row your data is on!
 
Last edited:
Upvote 0
Hi CakzPrimz,

Try this:

Code:
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
 
Upvote 0
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 (y)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top