Auto Add Row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Matlack - 23 May 2006 14:06 GMT

Hi!
I have a named range "Eng1" (B1:B5) and if the user enters data int
all 5 cells I want the range to expand to include (insert) a new row.
want the new row to be B6. In other words I want the bottom cell i
range "Eng1" to always be blank. It will start out 5 rows deep an
expand from there (no max) There are formulas in the adjacent cell
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!

Reply to this Message

Tom Ogilvy - 23 May 2006 14:24 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi!
> I have a named range "Eng1" (B1:B5) and if the user enters data into
[quoted text clipped - 7 lines]
>
> Any help or direction would be greatly appreciated! Thanks!!

Reply to this Message

Ivan Raiminius - 23 May 2006 14:24 GMT

Hi Brian,

you can define your named range "Eng1" to be
=OFFSET($A$1,0,0,MAX(5,COUNTA(A1:A65536)+1),1)

Regards,
Ivan

Reply to this Message

Ivan Raiminius - 23 May 2006 14:27 GMT

sorry, you wanted b1:b5

=OFFSET($b$1,0,0,MAX(5,COUNTA($b$1:$b$65536)+1),1)

regards,
Ivan

Reply to this Message

Brian Matlack - 23 May 2006 14:57 GMT

Tom:
The code worked fine. Thanks!! However! I failed to mention that
wanted to apply this code to a total of 35 different ranges in thi
worksheet. I can repeat the code 35 times with different range name
but I suspect there is a loop that will do it more efficiently. If yo
or anyone has time I would be interested to know how to do it.

Ivan:
I appreciate your suggestion I'll save it for future use. Thanks!

Reply to this Message

Ivan Raiminius - 23 May 2006 15:10 GMT

Hi Brian,

you can easily adapt Tom's code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
dim nName as name

On Error GoTo ErrHandler
for each nName in activeworkbook.names
Set rng = Range(nName)
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 = nName.name
rng(rng.Count).Offset(1, 1).Resize(1, 6).FillDown
End If
End If
next nName
ErrHandler:
Application.EnableEvents = True
End Sub

Regards,
Ivan

Reply to this Message

Brian Matlack - 23 May 2006 16:36 GMT

Ivan:
Thanks!! That worked great. I'm pretty much a rookie at this stuff a
you can tell. I do appreciate all the help from everyone on the Forum
It's a great tool with some realy great people involved.

Sorry for the editorial!! One last (silly) question.

How can I make the active cell (goto) the last cell in a named rang
ie. Range "eng1" is A1:A9 and I want the active cell to be A9 but i
the range definition changes to A1:A12 I now want to go to A12. What i
the range is defined as A1:D12 is there a way to get to A12 or B12 ect.

Thanks again for all the help!

Reply to this Message

Ivan Raiminius - 24 May 2006 08:32 GMT

Hi Brian,

added one row of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
dim nName as name

On Error GoTo ErrHandler
for each nName in activeworkbook.names
Set rng = Range(nName)
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 = nName.name
rng(rng.Count).Offset(1, 1).Resize(1, 6).FillDown
rng.cells(rng.rows.count,1).activate ' added
End If
End If
next nName
ErrHandler:
Application.EnableEvents = True
End Sub

Regards,
Ivan

Reply to this Message