adding row when user reaches the last available empty row

[Guest]

I have created a spreadsheet that is divided up into 3 sections... It looks
something like this: (I'll use customers as fake data to help make it easier
to understand)

CUSTOMER TYPE 1 INFORMATION
Charlie Brown 1234 Anywhere Drive Nowhere, ST
Sally Brown 5678 Nowhere Drive Anywhere, ST
Linus VanPelt 9101 Somewhere Drive Somewhere, ST

CUSTOMER TYPE 2 INFORMATION
Lucy VanPelt 4321 Anywhere Drive Nowhere, ST
Peppermint Patty 9876 Nowhere Drive Anywhere, ST

CUSTOMER TYPE 3 INFORMATION
Snoopy 5678 Somewhere Drive Somewhere, ST
Woodstock 4567 Nowhere Drive Nowhere, ST
Schroeder 2345 Anywhere Drive Anywhere, ST
Marcy 9635 Overthere Street Overhere, ST

Now what I have is a row or 2 of empty rows available for when users need to
add new data. However, they add new data frequently, and will be needing to
add rows often. I dont want to just add a bunch of available empty rows
between the 3 different sets of information, because I do not want it to
print with a bunch of empty lines. Also, I dont want to add a bunch of empty
rows and just hide them, because users may not know that there are hidden
rows available, and some do not know how to unhide rows. My 2 solutions to
this problem are:

1.) I need a way to prompt someone to ask them if they need a new row added
under "whichever section they need" when there is no longer an available row.
The kicker is that I need to keep on adding rows to the 3 different
sections.... so anytime the number of available rows runs out for the
selection they need, then it asks them if they would like to insert a new row
under section "Customers 2" (prompted because the last available row in
Customers 2 has just been used). A problem I see with this is that if
someone uses up the last line, and are finished in the worksheet, and they
get the prompt asking them if they need to add a new row under Customers 2,
they will naturally say "no' because they are done. But when the next person
enters that spreadsheet, they might need a new row under "Customers 2", and
they would need the prompt so that they could enter a row... Make sense?

2.) My other solution would be to create a macro that would always allow
for about 3-5 lines of empty rows, available for use under each section. If
someone is typing in one of the lines, as soon as they begin typing in that
row, excel recognizes that it will have one less available row, so it will
automatically add a new row to compensate for the other row being used.

Any help you can provide will be EXTREMELY useful!

Thanks!
Stacie

 

[Guest]

Also... these rows are formatted with borders, and with a certain color,
certain date formatting involved in a few columns, etc... How can I keep
adding rows with the same formatting?

 

[Guest]

A couple of things possible here... most of the solutions depend on how
comfortable you are with VBA. If you are not familiar with VBA then there are
some other solutions to explore...

Let me know how you feel about VBA...

 

[Guest]

I'm not that great with vba... I dont know much at all... I haven't done a
whole lot, but I've created macros and tweaked them some and learned how the
vba works according to what I know I wanted the macro to do. I've also done
a little bit in Access, but I by no means use it regularly, but can try to
deciper some stuff. I hope that helps.

 

[Guest]

Based on that here is my suggestion. Lets stay away from the VBA as much as
possible and stick with native Excel functionallity. Question on is can we
either split this one sheet up into 3 seperate sheets one for each customer
type or add in a column that indicates the customer type so that we can get
rid of the headings from each section of the sheet. This will make it
relatively easy to do...

 

[Guest]

I actually cannot change that. It has to remain in the same format
unfortunately. If the solution to this problem does involve some vb, I'm
more than willing to give it a try... At this point, I'm willing to try
anything to get it resolved.

Thanks again for your quick reply!

 

[Guest]

Here is the basics of what you want. It looks at the active cell and move up
or down to find the firs non blank row. It inserts a row beneth that and
copies the formats to that new row. This code should be pasted directly into
the sheet that contains the addresses. I am assuming that there are no
formulas in this sheet. This procedure can be called from a button that we
will add if that is what you need.

HTH

 

[Guest]

The procedure sounds good. There are no formulas on this sheet, that is
correct. Is there a way I can get the spreadsheet to run this procedure
without having to click a button? Can it run automatically when it realizes
that one of the blank rows has been filled with data? Does it have to be
tied to a button?

 

[Guest]

That is a lot more ugly as it will have to be tied to a change event which
recognizes that someone wants to add a new line (not modify an existing line
or delete a line). Definitely tricky and requiring a lot of work and
debugging. Stick with the button and you are home free.

HTH

 

[Guest]

Here is the code... I forgot to attch it to the lase E-mail...

Private Sub AddRow()
Dim rngAdd As Range

Set rngAdd = ActiveCell

If rngAdd.Value <> Empty Then
Set rngAdd = rngAdd.End(xlDown).Offset
Else
Set rngAdd = rngAdd.End(xlUp).Offset
End If

rngAdd.Offset(1, 0).EntireRow.Insert
rngAdd.EntireRow.Copy

Set rngAdd = rngAdd.Offset(1, 0)

rngAdd.PasteSpecial xlPasteFormats
Application.CutCopyMode = False

End Sub

 

[Guest]

I'll give it a try and see if it works, then let ya know. Thanks for all
your help!

 

[Dave D-C]

1) Why do Linus and Lucy have different addresses?
2) Sorry for (1)
3) How about having three 'Add' commandbuttons in column D,
to the right of the three sections. These would find the row where
the blank line is to be inserted and do the insert.
Add #1, #2, and #3 in column D on the CUST rows
(under the buttons) to mark the end of the previous section.
Then Buttons 1 and 2 would be like
Private Sub CommandButton1_Click()
Dim irow&
Cells(1, 1).Select ' select something on sheet
irow = Columns(4).Find("#2").Row
Rows(irow).Insert
Cells(irow, 1).Select
End Sub
and Button 3 would be
Private Sub CommandButton3_Click()
Dim irow&
Cells(1, 1).Select ' select something on sheet
irow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Cells(irow + 1, 1).Select
End Sub
This assumes no blank lines, but you could add code to check for
and add/delete blank lines.

 

[Guest]

Okay... I've tried both solutions, but can't get either to really work how I
want it to... Let me try to rephrase my new idea (since we've determined
using a button will be easiest).

This is what my spreadsheet looks like:

NEW WELLS (Add Row Button Next to Section Title)
Smith 1-1 Panola County Texas 1/25/05 J. Brown
Davis 1-2 Noble County Texas 2/1/05 B. Smith

EXPLORATORY WELLS (Add Row Button Next to Section Title)
Smith 1-1 Panola County Texas 1/25/05 J. Brown
Davis 1-2 Noble County Texas 2/1/05 B. Smith

UPCOMING NOTABLES (Add Row Button Next to Section Title)
Smith 1-1 Panola County Texas 1/25/05 J. Brown
Davis 1-2 Noble County Texas 2/1/05 B. Smith

What would the code behind each different button be to add a row at the
bottom of the corresponding section, of course, copying the formatting of
borders, background color, font color, etc... just not the values.

Thanks Again to both of you for your help!
Stacie

 

[Guest]

I'm glad your enjoying the post so much..hehe... I'm actually really enjoying
the project and making an otherwise difficult spreadsheet very clean and
automated. I certainly appreciate your help. I will try out your
suggestions tomorrow and let you know how it finally turns out... Again,
thanks so much for your assistance, you've been a tremendous help!

Stacie

I've sure enjoyed your post --
I've wanted something like this for a long time:

Two things:
1) to insert a row on demand:
Add a button on the last row.
1a) to insert a row before the buttonrow:
Private Sub CommandButton1_Click()
' insert row before button
Dim iRow&
iRow = CommandButton1.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow, 1).Select
Rows(iRow).Insert
End Sub
1b) to insert a row after the buttonrow:
Private Sub CommandButton2_Click()
' insert row after button
Dim iRow&
iRow = CommandButton2.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow + 1, 1).Select
' insert before; then copy; then clear
Rows(iRow).Insert
Rows(iRow + 1).Copy Rows(iRow)
Rows(iRow + 1).Clear
End Sub

But equally as great is the following 'totals' formula:
[e.g. in a4] =Sum(a1:Offset(a4,-1,0))
This has the advantage that the range will increase
when a row is inserted just before row 4.
=Sum(a1:a3) will not do this.

I sure this is not new, but I am thrilled!

I have created a spreadsheet that is divided up into 3 sections... It looks
something like this: (I'll use customers as fake data to help make it easier
to understand)

CUSTOMER TYPE 1 INFORMATION
Charlie Brown 1234 Anywhere Drive Nowhere, ST
Sally Brown 5678 Nowhere Drive Anywhere, ST
Linus VanPelt 9101 Somewhere Drive Somewhere, ST

CUSTOMER TYPE 2 INFORMATION
Lucy VanPelt 4321 Anywhere Drive Nowhere, ST
Peppermint Patty 9876 Nowhere Drive Anywhere, ST

CUSTOMER TYPE 3 INFORMATION
Snoopy 5678 Somewhere Drive Somewhere, ST
Woodstock 4567 Nowhere Drive Nowhere, ST
Schroeder 2345 Anywhere Drive Anywhere, ST
Marcy 9635 Overthere Street Overhere, ST

Now what I have is a row or 2 of empty rows available for when users need to
add new data. However, they add new data frequently, and will be needing to
add rows often. I dont want to just add a bunch of available empty rows
between the 3 different sets of information, because I do not want it to
print with a bunch of empty lines. Also, I dont want to add a bunch of empty
rows and just hide them, because users may not know that there are hidden
rows available, and some do not know how to unhide rows. My 2 solutions to
this problem are:

1.) I need a way to prompt someone to ask them if they need a new row added
under "whichever section they need" when there is no longer an available row.
The kicker is that I need to keep on adding rows to the 3 different
sections.... so anytime the number of available rows runs out for the
selection they need, then it asks them if they would like to insert a new row
under section "Customers 2" (prompted because the last available row in
Customers 2 has just been used). A problem I see with this is that if
someone uses up the last line, and are finished in the worksheet, and they
get the prompt asking them if they need to add a new row under Customers 2,
they will naturally say "no' because they are done. But when the next person
enters that spreadsheet, they might need a new row under "Customers 2", and
they would need the prompt so that they could enter a row... Make sense?

2.) My other solution would be to create a macro that would always allow
for about 3-5 lines of empty rows, available for use under each section. If
someone is typing in one of the lines, as soon as they begin typing in that
row, excel recognizes that it will have one less available row, so it will
automatically add a new row to compensate for the other row being used.

Any help you can provide will be EXTREMELY useful!

Thanks!
Stacie

 

[Dave D-C]

I've sure enjoyed your post --
I've wanted something like this for a long time:

Two things:
1) to insert a row on demand:
Add a button on the last row.
1a) to insert a row before the buttonrow:
Private Sub CommandButton1_Click()
' insert row before button
Dim iRow&
iRow = CommandButton1.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow, 1).Select
Rows(iRow).Insert
End Sub
1b) to insert a row after the buttonrow:
Private Sub CommandButton2_Click()
' insert row after button
Dim iRow&
iRow = CommandButton2.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow + 1, 1).Select
' insert before; then copy; then clear
Rows(iRow).Insert
Rows(iRow + 1).Copy Rows(iRow)
Rows(iRow + 1).Clear
End Sub

But equally as great is the following 'totals' formula:
[e.g. in a4] =Sum(a1:Offset(a4,-1,0))
This has the advantage that the range will increase
when a row is inserted just before row 4.
=Sum(a1:a3) will not do this.

I sure this is not new, but I am thrilled!

 

[Guest]

Works like a charm... BUT.. now how do I get it to keep the borders just like
the line above it? I used the code to insert a row above the button... It
currently keeps the same row shading (yellow), but I need it to keep the same
border (which is just the plain black border around each cell). I tried the
code below, however, it kinda goofs up and gives me 2 rows, and the top row
keeps the borders, and the second row is just yellow?!?!?! What am I missing
here?

Private Sub AddRow_Section1_Click()
' insert row before button
Dim iRow&
iRow = AddRow_Section1.TopLeftCell.Row
Cells(iRow, 1).Select
Rows(iRow).Insert
Selection.EntireRow.Insert
' select the range for borders
MyRow = ActiveCell.Row
MyRan = "A" & MyRow & ":" & "P" & MyRow
Range(MyRan).Select
' Now do borders
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'With Selection.Borders(xlInsideHorizontal)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
'End With
MyRan = "A" & MyRow
Range(MyRan).Select
End Sub

I'm glad your enjoying the post so much..hehe... I'm actually really enjoying
the project and making an otherwise difficult spreadsheet very clean and
automated. I certainly appreciate your help. I will try out your
suggestions tomorrow and let you know how it finally turns out... Again,
thanks so much for your assistance, you've been a tremendous help!

Stacie

I've sure enjoyed your post --
I've wanted something like this for a long time:

Two things:
1) to insert a row on demand:
Add a button on the last row.
1a) to insert a row before the buttonrow:
Private Sub CommandButton1_Click()
' insert row before button
Dim iRow&
iRow = CommandButton1.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow, 1).Select
Rows(iRow).Insert
End Sub
1b) to insert a row after the buttonrow:
Private Sub CommandButton2_Click()
' insert row after button
Dim iRow&
iRow = CommandButton2.TopLeftCell.Row
MsgBox iRow ' delete this
Cells(iRow + 1, 1).Select
' insert before; then copy; then clear
Rows(iRow).Insert
Rows(iRow + 1).Copy Rows(iRow)
Rows(iRow + 1).Clear
End Sub

But equally as great is the following 'totals' formula:
[e.g. in a4] =Sum(a1:Offset(a4,-1,0))
This has the advantage that the range will increase
when a row is inserted just before row 4.
=Sum(a1:a3) will not do this.

I sure this is not new, but I am thrilled!

I have created a spreadsheet that is divided up into 3 sections... It looks
something like this: (I'll use customers as fake data to help make it easier
to understand)

CUSTOMER TYPE 1 INFORMATION
Charlie Brown 1234 Anywhere Drive Nowhere, ST
Sally Brown 5678 Nowhere Drive Anywhere, ST
Linus VanPelt 9101 Somewhere Drive Somewhere, ST

CUSTOMER TYPE 2 INFORMATION
Lucy VanPelt 4321 Anywhere Drive Nowhere, ST
Peppermint Patty 9876 Nowhere Drive Anywhere, ST

CUSTOMER TYPE 3 INFORMATION
Snoopy 5678 Somewhere Drive Somewhere, ST
Woodstock 4567 Nowhere Drive Nowhere, ST
Schroeder 2345 Anywhere Drive Anywhere, ST
Marcy 9635 Overthere Street Overhere, ST

Now what I have is a row or 2 of empty rows available for when users need to
add new data. However, they add new data frequently, and will be needing to
add rows often. I dont want to just add a bunch of available empty rows
between the 3 different sets of information, because I do not want it to
print with a bunch of empty lines. Also, I dont want to add a bunch of empty
rows and just hide them, because users may not know that there are hidden
rows available, and some do not know how to unhide rows. My 2 solutions to
this problem are:

1.) I need a way to prompt someone to ask them if they need a new row added
under "whichever section they need" when there is no longer an available row.
The kicker is that I need to keep on adding rows to the 3 different
sections.... so anytime the number of available rows runs out for the
selection they need, then it asks them if they would like to insert a new row
under section "Customers 2" (prompted because the last available row in
Customers 2 has just been used). A problem I see with this is that if
someone uses up the last line, and are finished in the worksheet, and they
get the prompt asking them if they need to add a new row under Customers 2,
they will naturally say "no' because they are done. But when the next person
enters that spreadsheet, they might need a new row under "Customers 2", and
they would need the prompt so that they could enter a row... Make sense?

2.) My other solution would be to create a macro that would always allow
for about 3-5 lines of empty rows, available for use under each section. If
someone is typing in one of the lines, as soon as they begin typing in that
row, excel recognizes that it will have one less available row, so it will
automatically add a new row to compensate for the other row being used.

Any help you can provide will be EXTREMELY useful!

Thanks!
Stacie

 

[Guest]

One more question for you... I've created a button at the top of the form
that says "Move Row to Tracking Tab". When this button is clicked, I want it
to move the current row (where their cursor is), to the Tracking Tab, and add
it either at the top of the spreadsheet (under the header row) or to the very
bottom. The first cell that contains data on the Tracking Tab is A4.
Everything above that is the header. I've tried the following code, but cant
get it to work out right. It's giving me fits with the line
"Range("A4").Select", and I'm not really sure why.

Here is the code I used:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
Range("A4").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

 

[Guest]

Nevermind on this last issue about Moving Rows to Tracking Tab, I got that
one figured out.

 

[Guest]

I've got a spreadsheet where I've created a button and when it's clicked it
will move the current row to the appropriate tab and insert it there instead
of replacing it. It also deletes the old row. Now, for my question
regarding this:

My spreadsheet is divided up into 3 sections. For the sake of simplicity,
I'll just call them Well Type 1, Well Type 2 and Well Type 3. They are laid
out like this:

WELL TYPE 1 INFORMATION
Smith #1 County ST 1/13/05
Jones #2 County ST 1/14/05

WELL TYPE 2 INFORMATION
Brown #3 County ST 1/16/05

WELL TYPE 3 INFORMATION
Haynes #6 County ST 1/17/05

Currently, under the last row of information under each section, there is an
add button that when clicked will add a new row (formatted as necessary with
borders and background cell color) under the specified section. My workbook
has 2 tabs, Western, and Tracking. Both of these tabs look exactly the same
(just like shown above), the only difference is the data that exists in each
section.

My question is this: now that I've got it set up to move the rows and
delete the old row, and it's working fine, how do I make excel move it to the
right section? If they click on a cell that falls under Well Type 2, and hit
the Move Row to Tracking Tab button, how can I make excel know to move it
under the Well Type 2 section of the Tracking Tab? How can it look for a
section title and know to insert it under that section? Also, if it inserts
the row, will it keep the appropriate formatting???

I'm not all that great with vba, but am learning quickly. Any help is
greatly appreciated!

Stacie

 

[Dave D-C]

Single-stepping (F8) thru your code immediately shows

Rows(iRow).Insert
Selection.EntireRow.Insert

is what is giving you two inserted rows.
I'm learning a lot -- I've added comments to your code.

Private Sub AddRow_Section1_Click()
' insert row before button
Dim iRow&, MyRow&, MyRan$
iRow = Addrow_Section1.TopLeftCell.Row
Cells(iRow, 1).Select
Rows(iRow).Insert
' Selection.EntireRow.Insert ' delete this
' At this point you can (maybe) copy formats and formulae:
Rows(iRow - 1).Copy
Rows(iRow).PasteSpecial xlPasteFormats
Rows(iRow).PasteSpecial xlPasteFormulas
' Unfortunately, this also pastes values (XL97),
' so you might as well
Rows(iRow - 1).Copy Rows(iRow)
' instead of the above 3 lines.
' But you don't want old values:
Cells(iRow, 3) = "" ' .Clear clears formats
' There are probably things to change:
With Rows(iRow).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
'So I think this does it
' ' select the range for borders
' MyRow = ActiveCell.Row
' MyRan = "A" & MyRow & ":" & "P" & MyRow
' Range(MyRan).Select
' ' Now do borders
' Selection.Borders(xlDiagonalDown).LineStyle = xlNone
' Selection.Borders(xlDiagonalUp).LineStyle = xlNone
' With Selection.Borders(xlEdgeLeft)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' With Selection.Borders(xlEdgeTop)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' With Selection.Borders(xlEdgeBottom)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' With Selection.Borders(xlEdgeRight)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' With Selection.Borders(xlInsideVertical)
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' 'With Selection.Borders(xlInsideHorizontal)
' ' .LineStyle = xlContinuous
' ' .Weight = xlThin
' ' .ColorIndex = xlAutomatic
' 'End With
' MyRan = "A" & MyRow
' Range(MyRan).Select
' I would think better is
Rows(iRow).Select ' or
Cells(iRow, 1).Select
End Sub

Works like a charm... BUT.. now how do I get it to keep the borders just like
the line above it? I used the code to insert a row above the button... It
currently keeps the same row shading (yellow), but I need it to keep the same
border (which is just the plain black border around each cell). I tried the
code below, however, it kinda goofs up and gives me 2 rows, and the top row
keeps the borders, and the second row is just yellow?!?!?! What am I missing
here?

[code is above]