Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Add blank row at bottom of range
Hi,
What I want to do is the following: I have a worksheet formatted as a printable table, with heading rows, then the table value rows, and then a row that sums various columns. What I need to do is to find the last row in the table value rows, add a new row between this row and the row that sums the column values, and then put the info enetred in the form into this new row. It also must update the sum() field to include this row. As an example, my worksheet looks something like this: Heading Date: Ref | Location | Area | Comments | | | | | A | Left | 500 | LHS | B | Right | 325 | RHS | Totals | 825 | What I need to do is insert a row after Ref "B", and then add data there from a form. The totals must also be updated to reflect this added row. How can I do this? Thanks Brad Clarke |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Add blank row at bottom of range
When you say "add data there from a form" are you talking about a
userform? If so, assume that the values are in TextBox1 - TextBox4 and that the data is to be entered when CommandButton1 is clicked: Private Sub CommandButton1_Click() With Sheets("Sheet6").Range("A:A").Find( _ What:="Totals", _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=True) .EntireRow.Insert .Offset(-1, 0).Resize(1, 4).Value = Array(TextBox1.Text, _ TextBox2.Text, TextBox3.Text, TextBox4.Text) End With End Sub Your Totals cell should have a self-updating formula, something like (assuming the Area header is in C4, say): =SUM(OFFSET($C$4,,,ROW()-4,1)) so that no adjustment is necessary. In article , "Brad Clarke" wrote: Hi, What I want to do is the following: I have a worksheet formatted as a printable table, with heading rows, then the table value rows, and then a row that sums various columns. What I need to do is to find the last row in the table value rows, add a new row between this row and the row that sums the column values, and then put the info enetred in the form into this new row. It also must update the sum() field to include this row. As an example, my worksheet looks something like this: Heading Date: Ref | Location | Area | Comments | | | | | A | Left | 500 | LHS | B | Right | 325 | RHS | Totals | 825 | What I need to do is insert a row after Ref "B", and then add data there from a form. The totals must also be updated to reflect this added row. How can I do this? Thanks Brad Clarke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Add blank row at bottom of range
Hi, the quickest way to find the Totals row is to look for
the work 'Totals', although this assumes there are no other similar tables on different rows. Once you've done that, you just insert a row above the totals row. As long as you have done the formulas correctly, they should include the new row: set totalsCell = ws.Columns(1).Find _ what:="Totals", lookat := xlWhole. If Not totalsCell Is Nothing Then totalsCell.offset(-1, 0).EntireRow.Insert shift:=xlDown Call FunctionToInsertValuesInNewRow End If. You have to be careful after inserting a new row as the range TotalsCell will then refer to the first cell in the NEW row. Regards, Andy -----Original Message----- Hi, What I want to do is the following: I have a worksheet formatted as a printable table, with heading rows, then the table value rows, and then a row that sums various columns. What I need to do is to find the last row in the table value rows, add a new row between this row and the row that sums the column values, and then put the info enetred in the form into this new row. It also must update the sum() field to include this row. As an example, my worksheet looks something like this: Heading Date: Ref | Location | Area | Comments | | | | | A | Left | 500 | LHS | B | Right | 325 | RHS | Totals | 825 | What I need to do is insert a row after Ref "B", and then add data there from a form. The totals must also be updated to reflect this added row. How can I do this? Thanks Brad Clarke . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get rid of a blank row at bottom of Excel spreadsheet? | Excel Discussion (Misc queries) | |||
How do i delete blank lines at bottom of an Excel worksheet? | Excel Discussion (Misc queries) | |||
Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) | |||
Dynamic named list includes blank cell at bottom | Excel Discussion (Misc queries) | |||
How do I delete blank rows at the bottom of a spreadsheet to get . | Excel Discussion (Misc queries) |