VBA insert rows_dynamic

Question

Below is the piece of code in VBA which basically insert no of rows based on a count present in a specific cell, Now I want to change the code so that no of rows that will be inserted is to be one less then the count present in a specific cell. for eg- if in a specific column and specific cell count=N then macro will runn and add N no of rows.Now I want rows is to be added is one less i.e N-1

Sub InsertRowsIf()
Dim lr As Long, R As Range, i As Long
lr = Range("R" & Rows.Count).End(xlUp).Row
Set R = Range("R3", "R" & lr)
Application.ScreenUpdating = False
For i = R.Rows.Count To 1 Step -1
If IsNumeric(R.Cells(i, 1).Value) And Not IsEmpty(R.Cells(i, 1)) Then
R.Cells(i, 1).Offset(1, 0).Resize(R.Cells(i, 1).Value).EntireRow.Insert
End If
Next i


End Sub

change R.Rows.Count to R.Rows.Count -1 or 1 to 2 in your For i loop depending on whether you want to skip the bottom or top row. — Scott Craner
– Scott Craner, Commented Dec 21, 2018 at 19:20
This is not working as I had use [For i = R.Rows.Count - 1 To 1 Step -1] still adding same no of rows as the count in a cell. — Abhishek
– Abhishek, Commented Dec 21, 2018 at 19:39

Scott Craner · Accepted Answer · 2018-12-21 20:23:45Z

1

I think by trying to insert the use of R as a Range is causing issues. It is not needed.

Sub InsertRowsIf()

Dim ws As Worksheet
Set ws = Worksheets("Sheet1") ' Change to your sheet

Dim lr As Long
lr = ws.Range("R" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Dim i As Long
For i = lr To 3 Step -1
    If IsNumeric(ws.Cells(i, 18).Value) And ws.Cells(i, 18) <> "" Then
        ws.Cells(i, 1).Offset(1,0).Resize(ws.Cells(i, 18).Value - 1).EntireRow.Insert
    End If
Next i

Application.ScreenUpdating = True

End Sub

edited Dec 21, 2018 at 20:23

answered Dec 21, 2018 at 19:45

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Abhishek Over a year ago

My file name is "new insert rows" andsheet name is Sheet1 by using this showing out of range Set ws = Worksheets("new insert rows")

Scott Craner Over a year ago

1. Make sure you are using the latest version of my code above, I have made a few changes since first posting. 2. Make sure there are no spaces or other non printable characters that you did not include in the name in the vba. It would help if you copied the name from the sheet directly, or better yet use the code name.

Abhishek Over a year ago

Yes I am using the latest version of code, no error comes up but still adding same number of rows as the count

Scott Craner Over a year ago

@Abhishek AH, now I get what you are asking. See Edit.

Abhishek Over a year ago

Yes this is working,also my part of code also working the only thing I have to do is change value to value -1 in last line to make it happen.Thanks a lot @Scott.

urdearboy · Accepted Answer · 2018-12-21 19:36:45Z

0

You forgot to turn ScreenUpdating back on. Updated code to skip last row and applied standard indentation.

Option Explicit

Sub InsertRowsIf()

Dim lr As Long, R As Range, i As Long

lr = Range("R" & Rows.Count).End(xlUp).Row
Set R = Range("R3:R" & lr - 1)

Application.ScreenUpdating = False
    For i = R.Rows.Count To 1 Step -1
        If IsNumeric(R.Cells(i, 1).Value) And Not IsEmpty(R.Cells(i, 1)) Then
            R.Cells(i, 1).Offset(1, 0).Resize(R.Cells(i, 1).Value).EntireRow.Insert
        End If
    Next i
Application.ScreenUpdating = True

End Sub

answered Dec 21, 2018 at 19:36

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

1 Comment

Abhishek Over a year ago

Is it enough to add one less no of rows based on the count i have in a cell?

Collectives™ on Stack Overflow

VBA insert rows_dynamic

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related