Need to be able to insert row by either hot key or a button on the page

sburch032870

New Member
Joined
Jul 20, 2013
Messages
29
The sheet below is used in my company for estimating construction project costs. Sometimes we need 10 rows on the sheet, sometimes we need 110 rows. What I would like to be able to do is to insert a row above the current cell that I am in at the push of a button or the use of a hot key. For example I will setup a ******* line as a subtotal and then I need rows after that but I never know how many until I am doing it. I like to have the subtotal or key items in the sheet ahead of time so that I do mot miss anything but then I need an unknown amount of rows. I insert, copy and paste now but my my employees are not very good at it.

Any help would be appreciated very much.




-- removed inline image ---
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
That does insert a row and it is formatted but does not contain the formulas and conditions. It is a cool little trick though and I will use it until I can do it with all the other stuff in it. Thank you very, very m
uch...
 
Upvote 0
That does insert a row and it is formatted but does not contain the formulas and conditions.
Which row's formulas and conditions did you want to apply to the inserted row... those for the row you are on or for the row above the row you are on?
 
Upvote 0
I would like information from the row that I am in and have it insert the row above with the data/info from the row that I am in.
 
Upvote 0
See if this macro does what you want...
Code:
Sub InsertRowsCopyRowBelow()
  Dim Num As Long
  Num = CLng(Application.InputBox("How many rows do you want to insert?", "Get Insertion Count", 1))
  If Num > 0 Then
    ActiveCell.Resize(Num).EntireRow.Insert
    ActiveCell.Offset(Num).EntireRow.Copy ActiveCell.Resize(Num).EntireRow
  End If
End Sub
 
Upvote 0
Is there a way to clear the user entered contents (not formulas or formatting) from the row entered. Let's say I was working and for got to enter my row before I used the last empty row? I don't even know if this is possible, I wish that I had your knowledge of this program...... Thanks for the help on the other part thought it makes my life much easier, I insert about 200 rows per sheet when I am building a project. All that cutting and pasting is a huge hassle and always worries me about errors.
 
Upvote 0
Is there a way to clear the user entered contents (not formulas or formatting) from the row entered. Let's say I was working and for got to enter my row before I used the last empty row? I don't even know if this is possible, I wish that I had your knowledge of this program...... Thanks for the help on the other part thought it makes my life much easier, I insert about 200 rows per sheet when I am building a project. All that cutting and pasting is a huge hassle and always worries me about errors.
If I understand you correctly, you are looking for a method to delete only typed in values from the user. If that is correct, then select the entire row, press F5 to bring up the "Go To" dialog box, press the "Special..." button in the dialog box's lower left corner, select the option button labeled "Constants" and click the "OK" button. All the user entered data cells on the row will now be selected... simply press the Delete key on the keyboard (do not click the Delete button on the Ribbon... it is not the same thing) to clear just those values from the cells.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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