Hello,

Could someone please help with the following Excel 2003 AutoFill
problem?

I am trying to create a macro so that when a new row of data is added to
my worksheet, the formula in column J is automatically AutoFilled down
to the last row of data. I only add one row of data at a time. So, I
am basically only AutoFilling down one row at a time.

The worksheet is named: SP155_Register

The is a header row at row 2

The data rows start at row 3

The cell at J2 is called: STATUS

If anyone can please help, it would be greatly appreciated.

Thanks,

Chris.

Live Long and Prosper :-)

*** Sent via Developersdex http://www.developersdex.com ***

right click on the worksheet tab and select view code. Paste in code like
this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lrowA As Long, lrowj As Long
lrowA = Cells(Rows.Count, "A").End(xlUp).Row
lrowj = Cells(Rows.Count, "J").End(xlUp).Row
If lrowj < lrowA Then
For i = lrowj + 1 To lrowA
Cells(i, "J").FillDown
Next
End If
End Sub


--
Regards,
Tom Ogilvy


"Chris Hankin" wrote:

Hello,

Could someone please help with the following Excel 2003 AutoFill
problem?

I am trying to create a macro so that when a new row of data is added to
my worksheet, the formula in column J is automatically AutoFilled down
to the last row of data. I only add one row of data at a time. So, I
am basically only AutoFilling down one row at a time.

The worksheet is named: SP155_Register

The is a header row at row 2

The data rows start at row 3

The cell at J2 is called: STATUS

If anyone can please help, it would be greatly appreciated.

Thanks,

Chris.

Live Long and Prosper :-)

*** Sent via Developersdex http://www.developersdex.com ***

This is a tricky routine. Had to consider deleting rows as well as adding
row. The function doesn't do anythin if you add or delete row 3.

Private Sub Worksheet_Change(ByVal Target As Range)


Const StartCell = "J3"

' Prevent endless loops - changing cell causes
' routine to execute recursively
' Target address for insert row looks like $8:$8
If InStr(Target.Address, ":") < 0 Then


LeftAddress = Left(Target.Address, InStr(Target.Address, ":") - 1)

' row 3 exit routine because we don't know what to do
If (StrComp(Mid(LeftAddress, 2), "3") = 0) Then Exit Sub

'If column exit routine
If (IsNumeric(Mid(LeftAddress, 2)) = False) Then Exit Sub


RightAddress = Mid(Target.Address, InStr(Target.Address, ":") + 1)
If StrComp(LeftAddress, RightAddress) = 0 Then


RowCount = 0
Do While (1)
Destination1 = Range(StartCell). _
Offset(rowoffset:=RowCount + 1, columnoffset:=0)
Destination2 = Range(StartCell). _
Offset(rowoffset:=RowCount + 2, columnoffset:=0)

'thses statement are needed if rows are deleted
MyError = IsError(Destination) Or IsError(Destination2)
If MyError = False Then

If (Destination1 = "") And (Destination2 = "") Then

Exit Do

End If
End If

Range(StartCell). _
Offset(rowoffset:=RowCount, columnoffset:=0).Copy _
Destination:=Range(StartCell).Cells. _
Offset(rowoffset:=RowCount + 1, columnoffset:=0)

RowCount = RowCount + 1
Loop
End If



End If




End Sub


"Chris Hankin" wrote:

Hello,

Could someone please help with the following Excel 2003 AutoFill
problem?

I am trying to create a macro so that when a new row of data is added to
my worksheet, the formula in column J is automatically AutoFilled down
to the last row of data. I only add one row of data at a time. So, I
am basically only AutoFilling down one row at a time.

The worksheet is named: SP155_Register

The is a header row at row 2

The data rows start at row 3

The cell at J2 is called: STATUS

If anyone can please help, it would be greatly appreciated.

Thanks,

Chris.

Live Long and Prosper :-)

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Tom and Joel,

Due to time differences in Australia, I will let you know how I went
with your codes. Thanks again for all your efforts - very much
appreciated.

Cheers,

Chris.

Live Long and Prosper :-)

*** Sent via Developersdex http://www.developersdex.com ***

Hello Tom and Joel,

Your codes worked very well thanks.

Cheers,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***