Insert rows then format cell in new row

CEMrob

New Member
Joined
Apr 11, 2013
Messages
4
Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:
1. insert a row below the current row (whatever cell happens to be selected)
2. then remove the left hand border of the cell in column B in the new row,
3. then select the adjacent cell in column C in the new row ready for data entry.
Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down. All suggestions gratefully received.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
What do you want to trigger the addition of the new row? Do you want to insert it when you click on a cell?
 
Upvote 0
Try this code. You can assign a key or a button on your worksheet to run it.
Code:
Sub InsertRow()
    Rows(ActiveCell + 1).Insert
    ActiveCell.Borders(xlEdgeLeft).LineStyle = xlNone
    ActiveCell.Offset(0, 1).Select
End Sub
 
Upvote 0
My apologies. Please try this code. The previous only worked if the active cell was empty for some reason.
Code:
Sub InsertRow()
    Dim x As Integer
    x = ActiveCell.Row
    Rows(x + 1).Insert
    ActiveCell.Borders(xlEdgeLeft).LineStyle = xlNone
    ActiveCell.Offset(0, 1).Select
End Sub
 
Upvote 0
Thanks very much, but I get an error 1004 "application-defined or object-defined error" on Rows(ActiveCell + 1).Insert.
 
Upvote 0
Got there! Sorry, my last post overtaken by your last post! Using your second version, the new row is inserted correctly but the cell formatting and movement all occur in the original row. I inserted "ActiveCell.Offset(1, 0).Select" after the insert, and all seems fine now. Many thanks indeed.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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