Best way to use a macro to insert new rows

[John T]

I have a workbook that has data in range A5:J54.
Sometimes a row will need to be added if that range is full but only for columns A:J.
I'm not sure if its best to have a button that just inserts a new row every time its pressed below the last entry in that row or to have a button that when pressed asks the user how many rows they would like to add.
Could somebody give me a code for the best option please.

Currently in row 55 and below i have fixed data so this will move down when rows are inserted. for example in cell A55 i have the word "Bodyshop". In other cells i have sub totals.

Many Thanks.

 

[Yongle]

There are several ways to do this and the most appropriate is dependant on what the user is required to do immediately afterwards

Q1 Is the inserted row an empty row without any formatting or formulas ?

Sometimes a row will need to be added if that range is full but only for columns A:J

Q2 What is the reason for inserting only A:J rather than the whole row ?

Q3 How will the user use the newly-inserted row ?
-eg manual input into every cell, etc ??

Q4 Why would it be helpful to the user to have multiple rows inserted ?

 

[John T]

Thanks for replying.

Q1 There is a formula in column J (=SUM(D54:I54)-C54). At the moment if i highlight cells A55:J55 and right click, insert row. That does what i want and keeps the formula.
Q2 I have fixed data to the right and below this cell range. The data to the right i don't want to move down.
Q3 Manually input data
Q4 Currently they have the 50 rows between A5:J54 to enter data. Occasionally they need extras rows if there is more data. I want them to be able to click a button and it to add an additional row if needed.

 

[Yongle]

Is this what you want?
- code looks for cell with value bodyshop ( case insensitive search ) and inserts row above (A:J only) with formula in column J only

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

51​

[/td][td]BEFORE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]FORMULA in J[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

52​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D52:I52)-C52[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

53​

[/td][td]

1​

[/td][td]

3​

[/td][td]

5​

[/td][td]

7​

[/td][td]

9​

[/td][td]

11​

[/td][td]

13​

[/td][td]

15​

[/td][td]

17​

[/td][td]

67​

[/td][td] =SUM(D53:I53)-C53[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

54​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td]

10​

[/td][td]

12​

[/td][td]

14​

[/td][td]

16​

[/td][td]

18​

[/td][td]

72​

[/td][td] =SUM(D54:I54)-C54[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

55​

[/td][td=bgcolor:#E2EFDA]BodyShop[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

51​

[/td][td]AFTER 1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]FORMULA in J[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

52​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D52:I52)-C52[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

53​

[/td][td]

1​

[/td][td]

3​

[/td][td]

5​

[/td][td]

7​

[/td][td]

9​

[/td][td]

11​

[/td][td]

13​

[/td][td]

15​

[/td][td]

17​

[/td][td]

67​

[/td][td] =SUM(D53:I53)-C53[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

54​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td]

10​

[/td][td]

12​

[/td][td]

14​

[/td][td]

16​

[/td][td]

18​

[/td][td]

72​

[/td][td] =SUM(D54:I54)-C54[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

55​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D55:I55)-C55[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

56​

[/td][td=bgcolor:#E2EFDA]BodyShop[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

51​

[/td][td]AFTER 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]FORMULA in J[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

52​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D52:I52)-C52[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

53​

[/td][td]

1​

[/td][td]

3​

[/td][td]

5​

[/td][td]

7​

[/td][td]

9​

[/td][td]

11​

[/td][td]

13​

[/td][td]

15​

[/td][td]

17​

[/td][td]

67​

[/td][td] =SUM(D53:I53)-C53[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

54​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td]

10​

[/td][td]

12​

[/td][td]

14​

[/td][td]

16​

[/td][td]

18​

[/td][td]

72​

[/td][td] =SUM(D54:I54)-C54[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

55​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D55:I55)-C55[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

56​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td] =SUM(D56:I56)-C56[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

57​

[/td][td=bgcolor:#E2EFDA]BodyShop[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA]etc[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]


Code

Sub InsertRow()
    With ActiveSheet.Range("A:A").Find("bodyshop", MatchCase:=False, LookAt:=xlWhole)
        .Resize(, 10).Insert Shift:=xlDown
        .Offset(-2, 9).Copy .Offset(-1, 9)
    End With
End Sub

 

[John T]

Thanks. That appears to work great.