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


"Stacie Fugate" wrote:

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?

"Jim Thomlinson" wrote:

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

"Stacie Fugate" wrote:

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!

"Jim Thomlinson" wrote:

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...

"Stacie Fugate" wrote:

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.

"Jim Thomlinson" wrote:

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...

"Stacie Fugate" wrote:

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?

"Stacie Fugate" wrote:

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 a

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