Insert New Rows Via VBA Excel

jenely88

New Member
Joined
Nov 25, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am new to here today and I have been searching everywhere for an answer that will help me to no luck. I am looking to use some VBA code to do the following:

In a row, when columns N, O or P have data in the cells then a new row needs to be inserted below and copy the data from the cells that are in columns N, O or P. So for example if we look at the image (Screenshot 2021-11-25 170033) i have attached, row 2 has no information in the N2, O2 or P2 cells so therefore a new row under this is not needed. Row three has information in the N3, O3 and P3 cells so i would need a new row inserting under row 3 and then the information from N3, O3 and P3 pasting directly underneath.

I have added another screen show to show what it would look like if the VBA code worked (Screenshot 2021-11-25 170255).

I hope this makes sense and someone can help me to achieve this.

Many Thanks

Jen
 

Attachments

  • Screenshot 2021-11-25 170033.png
    Screenshot 2021-11-25 170033.png
    52.3 KB · Views: 27
  • Screenshot 2021-11-25 170255.png
    Screenshot 2021-11-25 170255.png
    37.6 KB · Views: 27

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Have a backup of your data before you try the code.

VBA Code:
Option Explicit

Public wb As Workbook
Public ws As Worksheet

Sub Macro1()
    Dim i As Long, ctr As Integer
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")      'Update as required
    
    For i = 1 To 1048576
        If NOP(i) Then
            ws.Rows(i + 1).Insert
            ws.Cells(i + 1, 14) = ws.Cells(i, 14)
            ws.Cells(i + 1, 15) = ws.Cells(i, 15)
            ws.Cells(i + 1, 16) = ws.Cells(i, 16)
            i = i + 1
        End If
    Next i
End Sub

Function NOP(i As Long) As Boolean
    NOP = Not IsEmpty(ws.Cells(i, 14)) And Not IsEmpty(ws.Cells(i, 15)) And Not IsEmpty(ws.Cells(i, 16))
End Function
 
Upvote 0
Welcome to the MrExcel board!

Here is another option to try with a copy of your workbook.
I have assumed that the last row with data can be determined from column A as shown in your image.

VBA Code:
Sub Insert_Rows()
  Dim a As Variant
  Dim i As Long
 
  Application.ScreenUpdating = False
  For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    a = Application.Index(Range("N" & i).Resize(, 3).Value, 1, 0)
    If Len(Join(a, "")) > 0 Then
      Rows(i + 1).Insert
      Range("N" & i + 1).Resize(, 3).Value = a
    End If
  Next i
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Hi both,

thank you very much! both work and i am really grateful for your help with this, it will make a long process a lot quicker now!!
 
Upvote 0
Sub Insert_Rows() Dim a As Variant Dim i As Long Application.ScreenUpdating = False For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 a = Application.Index(Range("N" & i).Resize(, 3).Value, 1, 0) If Len(Join(a, "")) > 0 Then Rows(i + 1).Insert Range("N" & i + 1).Resize(, 3).Value = a End If Next i Application.ScreenUpdating = True End Sub
Hi peter Quick question, I have looked at this for a long time and tried to work it out, the code you have provided works amazingly but if I wanted to change the range of cells that were been copied into the new row how would I go about that. Let says there was a mixture such as A, B, D, E and AG, and for those to be put in the corresponding cells below.

I can think of so many uses for this now :)
 
Upvote 0
Quick question,
.. but not a quick answer. ;)
The original question was to move 3 consecutive cells. Now you are asking about non-consecutive cells.

Let says there was a mixture such as A, B, D, E and AG
Is the condition for adding a new row that any one or more of those particular cells has an entry?
.. or is it still that at least one cell in columns N:P has an entry?


Given that it now sounds a bit like column A may or may not have an entry, is there a particular column that we can rely on to determine where the bottom row of the entire data is?
 
Upvote 0
Assuming the conditions remains same, try the following code
The code is a bit ugly, but should work

VBA Code:
Option Explicit

Public wb As Workbook
Public ws As Worksheet

Sub Macro1()
    Dim i As Long, ctr As Integer
 
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")      'Update as required
 
    For i = 1 To 1048576
        If NOP(i) Then
            ws.Rows(i + 1).Insert
'            ws.Cells(i + 1, J) = ws.Cells(i, J)            Where J is column number
            ws.Cells(i + 1, 1) = ws.Cells(i, 1)             '1 for A
            ws.Cells(i + 1, 2) = ws.Cells(i, 2)             '2 for B
            ws.Cells(i + 1, 4) = ws.Cells(i, 4)             '4 for D
            ws.Cells(i + 1, 5) = ws.Cells(i, 5)             '5 for E
            ws.Cells(i + 1, 14) = ws.Cells(i, 14)           '14 for N
            ws.Cells(i + 1, 15) = ws.Cells(i, 15)           '15 for O
            ws.Cells(i + 1, 16) = ws.Cells(i, 16)           '16 for P
            ws.Cells(i + 1, 33) = ws.Cells(i, 33)           '33 for AH
            i = i + 1
        End If
    Next i
End Sub

Function NOP(i As Long) As Boolean
    NOP = Not IsEmpty(ws.Cells(i, 14)) And Not IsEmpty(ws.Cells(i, 15)) And Not IsEmpty(ws.Cells(i, 16))
End Function
 
Upvote 0
.. but not a quick answer. ;)
The original question was to move 3 consecutive cells. Now you are asking about non-consecutive cells.


Is the condition for adding a new row that any one or more of those particular cells has an entry?
.. or is it still that at least one cell in columns N:P has an entry?


Given that it now sounds a bit like column A may or may not have an entry, is there a particular column that we can rely on to determine where the bottom row of the entire data is?
Thanks Peter it was more of a curiosity question. I agree non of the answers look easy! thank you for replying no need to provide code for this i just wondered if it was possible thats all. I am trying to understand how the code works. Thank you very much for replying!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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