Solved: Insert Rows basis value in Cell

[JohnyG]

i need some help.

In Excel sheet i have some data that start from A3-D9. I want a functionality that allows me to enter any numeric value in Cell A2. Let say I entered 2 then it will insert 2 blank rows before A3. The value in Cell A2 keeps on changing by user so let say if user enter 5 then 5 blank rows needs to be inserted.

It shud only haapen when a user changes values of Cell C2.

Please Help

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Me.Rows(3).Resize(.Value).Insert
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[JohnyG]

Thanks xld ..

[Lauradk50]

Hi,
Excellent code below thanks, as I have a similar issue. However my worksheet range is not a constant, i.e. A2, mine is different for each row when working through the list of data.
On mine, users won't enter values. The values are already in and I'm working through a list of data where for each user ID, I need to insert rows according to the value of the cell on the left of each User ID. I wrote some code, but it is totally different to what you have written. Copy attached. I have managed to get it working, except for the one line where it I cannot get it to read the value of the next row to insert the number of rows required for that user ID. The problem line is setting the value of 'N'.

Please could you advise how I could amend this to accommodate this?
Many thanks, Laura

Your Code:



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Me.Rows(3).Resize(.Value).Insert
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

My Code:

Sub Test_InsertRowtoNValue()
Dim lrow As Long
Dim Firstrw As Long
Dim LastRw As Long
Dim N As Integer
Dim i As Variant
Firstrw = ActiveSheet.UsedRange.Cells(4, 4).Row
LastRw = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
ActiveSheet.Rows(LastRw).Select
N = Range("C4").End(xlDown).Value
Do While Not IsEmpty(N - 1)

For LastRw = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
With N = Range("C4").End(xlDown).Value
If Cells(LastRw, "D") <> Cells(LastRw - 1, "D") Then _
Rows(LastRw).Resize(N).EntireRow.Insert
End With
Next LastRw
Loop
End Sub

[Bob Phillips]

Laura,

Can you post an example workbook. I am getting a bit confused by where the data is located from your code, something seeing the data will instantly clarify. If the data rlocation could be variable, maybe post two examples on different sheets.

[Lauradk50]

Hi,
I've attached an abbreviated version of a large volume of data to give you an idea. What I was actually trying to do from the start is merge 2 different worksheets data into one workbook. The only unique field being the User Name. Per the worksheet attached you'll see the headings (the 1st section is from the 1st worksheet, and the yellow highlighted bit is from the 2nd worksheet). The problem was that for every number of rows that appeared in the Live Users worksheet, each row had to be duplicated by the number of rows for that same user in the Approval Limits worksheet. The only way I could think of doing it was to merge the data on a pivot table and then insert the formula
"=IF(GETPIVOTDATA("Value",Sheet4!$A4,"Row",Result!D6,"Column","Application Name")=0,1,GETPIVOTDATA("Value",Sheet4!$A$3,"Row",Result!D6,"Column","Application Name"))" to calculate the number of rows in each worksheet per User, then multiple the 2 together (refer to column C).
From there I wanted to insert the total number of required rows for each user & then try & work out a way to create a vlookup to pick up the relevant data from each worksheet.
Hope all that makes sense, but please let me know if you need further info. Unfortunately my pc at work only has Excel 2000, and could not cope with the formulae so I had to make them values so you see the actual values and what I was trying to achieve.
Any advise would be greatly appreciated - thank you.
Laura

[Bob Phillips]

Laura,

I can't pretend that I understand what you are doing to get where you want to be, but the code you need seems quite clear, so here goes



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

.Rows(i + 1).Resize(.Cells(i, "C").Value).Insert
Next i
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

[Lauradk50]

Dear "xld",

You are an absolute star!. I've just tried it and it's EXACTLY what I was trying to achieve and works like a dream! THANK YOU ever so much for your valued input - so VERY much apreciated.
Laura :bow: