Add a Row

[Jamie]

I am working in Excel 2002.

I have a table that currently is 14 rows. The first 4 contain the title and
row headings. The last row, 14, is a Total row. There are 2 columns that
have formulas these formulas add information from the cells to the right of
them. The Total row, 14, totals all the figures above it.

What I would like to do is, when the user has completed the last row before
the Total row, they can add a new blank row. This row would need to keep the
formatting and contain the formulas like the rows above it. I’m not sure,
coding wise, how to go about this. Or how the user would run the macro;
through a keyboard shortcut key, a command button, etc.

Any suggestions you might have is greatly appreciated.

 

[Chip]

This should get you started:
Sub insertrow()
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcolumn = ActiveSheet.UsedRange.Columns.Count
Rows(lastrow).Select
Selection.Insert Shift:=xlDown
Cells(lastrow - 1, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select
Selection.AutoFill Destination:=Range(ActiveCell,
ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault
End Sub

As for how it gets started you have the option to have the macro run
with a shortcut, when a certain cell is selected, when a certain
cell's value is changed, on the click of a command button...i would
need to know what u want tho for me to help u on that

 

[Kou Vang]

Perhaps a user prompt to initiate the insertion of the row? Sounds wierd,
but that way there would be no buttons.

Dim Msg, Style, Title, Response,
Msg = "Do you want to insert row?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
rows("13:13").insert ' Perform some action.
End If

Kou

 

[Jamie]

Hi Chip, thanks for your response. When I run the macro, I receive the
following error message:

Run-time Error '1004'
Application-defined or object defined error

When I debug the coding the following line is highlighted:

Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select

I'm sure you know what that means, but I haven't a clue.

 

[Jamie]

Kou, this works great, thanks. I have another question for you.

Suppose my table range is A1:U6. Row 5 is a blank and row 6 is my total
row. Is there a way to set the tabbing so, if I'm in cell U5 and I hit the
tab button the cursor will go to the first cell in that row, in this case
cell A5.

 

[Kou Vang]

I don't believe you can do that...but u might want to try asking in the
worksheets.functions ng?

Kou

 

[Jamie]

Thanks Kou, you've been very helpful.