VBA Code, Select Entire Row, Insert New Row, Select Original Active Cell Again

Chris Williamson

Board Regular
Joined
Oct 16, 2010
Messages
83
Hi Guys,

I found part of this code in another topic in these fantastic forums, but I just need a little hand to personalise it a little bit more for what I would like it to do.


Code:
Sub Insert()
    Selection.EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
End Sub

This works just fine to insert a new row wherever the selected cell is on the worksheet and then to select cell C2 to finish off.

But what I would like to do is once the new row is inserted, I would like to select whatever the original cell was in the first place.

For example, if cell C10 is selceted, click the button to run this code and for then it to select cell C10 to finish off.

For example, if cell C17 is selceted, click the button to run this code and for then it to select cell C17 to finish off.

So this line of the code "Range("C2").Select" needs to somehow select the original cell that was selected in the first place... any ideas?

Thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You don't need to select

Code:
Sub Insert()
Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
 
Upvote 0
Bravo,

Many thank you's.
It makes sence now... don't select the entire row in the first place then I wont have this problem haha.

Regards
 
Upvote 0
Chris

Is there more code?

I don't see AutoFit in what you've posted so far.:)
 
Upvote 0
No it is just this

Code:
Sub Insert()
Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

I am however inserting this into a tabel (alt T), but I don't think that will matter? Will it... :eek:
 
Upvote 0
Chris

I had a feeling there was some change event code involved somewhere.

Now that you've found the root of the problem have you fixed it, or do you still need help?

I think that if you either removed the AutoFit or changed it to just autofit the column(s) you are interested in then it might work.

You might also want to add a further check on the Target parameter.

One other thing that might help would be to turn off events.

You can do that like this.
Code:
Application.EnableEvents = False
 
' code for change event
 
Application.EnableEvents = True
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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