Start with the change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo ErrHandler
Set rng = Range("eng1")
If Not Intersect(rng, Target) Is Nothing Then
If rng.Count = Application.CountA(rng) Then
Application.EnableEvents = False
rng.Resize(rng.Rows.Count + 1, 1).Name = "eng1"
rng(rng.Count).Offset(1, 1).Resize(1, 6).FillDown
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


If your not familiar with events, then see Chip Pearson's page providing an
overview
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Brian Matlack" wrote:


Hi!
I have a named range "Eng1" (B1:B5) and if the user enters data into
all 5 cells I want the range to expand to include (insert) a new row. I
want the new row to be B6. In other words I want the bottom cell in
range "Eng1" to always be blank. It will start out 5 rows deep and
expand from there (no max) There are formulas in the adjacent cells
that I want to fill down when the new row is added.

I know this is possible but I have no clue where to start.

Any help or direction would be greatly appreciated! Thanks!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=544668