Hello
With the code below am able to Insert Cells and move downwards but not able to maintain the values of Column A which is in First Row of Selected Range which shall remain always.
So what happens when i insert Value of B in 1st row of selected range Value of Column A moves downwards too.
Illustrations of 3 examples for reference
How to maintian all the values of Column A in its 1st row of the Selected Range inspite of inserting the values in between the range as well 1st row of the selected Range
Indeed your help will be Great
NimishK
Illustrations of examples
Original
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]Original[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD]Sel.Range for Hello is A1:C3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD]Sel.Range for World is A4:C6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 1[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]1xa[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 2[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]H1[/TD]
[TD]hx[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 3[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]W2[/TD]
[TD]wx[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks
With the code below am able to Insert Cells and move downwards but not able to maintain the values of Column A which is in First Row of Selected Range which shall remain always.
So what happens when i insert Value of B in 1st row of selected range Value of Column A moves downwards too.
Illustrations of 3 examples for reference
How to maintian all the values of Column A in its 1st row of the Selected Range inspite of inserting the values in between the range as well 1st row of the selected Range
Indeed your help will be Great
NimishK
Code:
Private Sub UserForm_Initialize()
CurRec = 1
End sub
Private Sub Insert_Click()
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet1")
Ws.Activate
Dim curRow As Long
Dim FirstMinRow As Long
Dim lastMaxRow As Long
Dim nosRows As Long
Dim RowsDiff As Long
FirstMinRow = Ws.Range(txtRangeAddress.Text).Rows(curRec).Row '1st row of Selected range
lastMaxRow = FirstMinRow + Ws.Range(txtRangeAddress.Text).Rows.Count - 1 'last row of the Selected Range
nosRows = Ws.Range(txtRangeAddress.Text).Rows.Count
curRow = Val(FirstMinRow)
Ws.Range(Cells(curRow, 1), Cells(curRow, 3)).Insert
End Sub
Original
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]Original[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD]Sel.Range for Hello is A1:C3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD]Sel.Range for World is A4:C6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 1[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1A[/TD]
[TD]1xa[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 2[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]H1[/TD]
[TD]hx[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Inserted Eg 3[/TD]
[TD][/TD]
[TD]After Insert_button Pressed[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]hello[/TD]
[TD]1[/TD]
[TD]1x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]World[/TD]
[TD]aa[/TD]
[TD]ax[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]bb[/TD]
[TD]bx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]W2[/TD]
[TD]wx[/TD]
[TD]Want the result like this[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]cc[/TD]
[TD]cx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks
Last edited: