automatically inserting rows

[Southern at Heart]

I have an expense report I made. The Heading across the top of the columns
are:
Date Description Mileage Hours Supplies Total

then at the bottom it tallys up these, and has a few other things like
hourly wages...

Sometimes a job might have 30 lines, and other time 100 lines. Is there a
way to have the footer part of my sheadsheet automatically adjust (add lines
as I use them) so that I don't have to have my footer way down, off the
screen, when I'm doing a smaller job. But if it does automatically add lines
in, then those lines will need to be tallied in the totals in the footer
section.
I would be great if there were a feature that would do this: It would just
give be 1 blank row before the footer. And then when I put data in that row
for that day, it would add another blank row, so it would automatlcally grow
in this fashion.
thanks,
southern at heart

 

[Don Guillett]

Footer is not the proper word for the last row
However, IF? you are saying that you want to enter info in the row just
before the last row and have a row inserted between that data and the last
row then right click the sheet tab>view code>copy/paste this. Now,when you
add data in the next to the last row of column A, the row will auto insert
below.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Row = _
Cells(Rows.Count, 1).End(xlUp).Row - 1 Then
Rows(Target.Row + 1).insert
End If
Application.EnableEvents = True
End Sub

Sub fixifbroke()
Application.EnableEvents = True
End Sub

 

[Southern at Heart]

SIMPLY LOVELY!
One more thing. My 'footer'/last row actually consists of 3 rows. Can I
change that code to accommodate 3 rows?
thanks so much.

 

[Shane Devenshire]

Hi,

Maybe you should consider AutoFilter. Suppose you consider any of your
columns which will have data if you have entries, when you apply an
AutoFilter to the range from the headers all the way down to the row you are
calling a footer (where the totals are) then you can oppen the AutoFilter for
one of these columns and choose Not Blank. his will hide all the blank rows.
You can unhide these by setting the filter back to (All). Regardless of how
much data you have or don't you can always see just the rows with data, in
effect removing blank rows.

If this helps, please click the yes button

cheers,
Shane Devenshire

 

[Roger Govier]

Hi

If you just change the part of Don's code from
Row - 1 Then
to
Row - 3 Then

it will do that for you.

 

[Jamie]

Dear Don et al.,

I have a similar issue to this, but not the same. I work for a packaging
company, and I want to create a sheet for quoting clients. If they require
one wooden crate they click yes on a drop down validation screen, I then want
a series of rows (3 to be exact) to drop into the screen which are the
dimensions of the required crate as well as the type of crate. I then want
the sheet to ask if I want another crate, if yes is answered, a further 3
lines drop in. If the answer is no to additional crates, nothing more is
dropped in.

The crates are just part of the quotation process not the last line, hence
my question is similar, but not the same.

Is this possible?

Many thanks
Jamie

 

[Roger Govier]

Hi Jamie

You could insert a new column in front of your quotes layout.
I used column A.
I also had my quotation layout starting at Row 8, change to suit your case.

The following Event code, allows you to clcik on a cell in column A within
the range of our quotation.
The code then asks whether you wish to insert Case details.
If you say Yes, the code will insert 3 lines immediately below the line on
which you clicked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub ' more than 1 cell selected
If Target.Column > 1 Then Exit Sub ' not column A, change to suit
If Target.Row < 8 Then Exit Sub ' before row 8, change to suit

Select Case MsgBox("Do you want to Insert Case details?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, Application.Name)

Case vbNo
Exit Sub
Case vbYes
End Select

With Target
..Offset(1).Resize(3).EntireRow.Insert Shift:=xlDown
End With

End Sub

Copy the Code.
Right click on the worksheet tab
View code
Paste the code into the white pane that appears.
Alt + F11 to return to Excel