Inserting Cells of row in Selected Range in a Particular way

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
688
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

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
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
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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