Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
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. Im 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. -- Jamie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
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. "Chip" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
I don't believe you can do that...but u might want to try asking in the
worksheets.functions ng? Kou |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Row
Thanks Kou, you've been very helpful.
"Kou Vang" wrote: I don't believe you can do that...but u might want to try asking in the worksheets.functions ng? Kou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|