Inserting Cells of row in Selected Range in a Particular way

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
684
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
Original
ABC
1hello11xSel.Range for Hello is A1:C3
222x
333x
4WorldaaaxSel.Range for World is A4:C6
5bbbx
6cccx
Inserted Eg 1After Insert_button Pressed
1hello11x
21A1xaWant the result like this
322x
433x
5Worldaaax
6bbbx
7cccx
Inserted Eg 2After Insert_button Pressed
1helloH1hxWant the result like this
211x
322x
433x
5Worldaaax
6bbbx
7cccx
Inserted Eg 3After Insert_button Pressed
1hello11x
222x
333x
4Worldaaax
5bbbx
6W2wxWant the result like this
7cccx

<tbody>
</tbody>

Thanks
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,216,076
Messages
6,128,670
Members
449,463
Latest member
Jojomen56

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