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

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

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


.