Using worksheet_change to add row to named range

[wbstadeli]

Hi all,

How do i use the worksheet_change event to do the following? My named range is "Timelog".

If first cell of last row in named range "Timelog" is blank, then do nothing, if not blank, then add row to bottom of named range "Timelog". Thanks for your help!

 

[Roderick_E]

Call this macro... change Sheet1 to your sheet.

Sub testnr()
Dim nradd As String
Dim nr As Name
Set nr = ActiveWorkbook.Names.Item("TimeLog")
nradd = Sheet1.Range("TimeLog").AddressLocal
If Trim(Sheet1.Range(Mid(nradd, InStr(nradd, ":") + 1, Len(nradd)))) <> "" Then
nr.RefersTo = "=Sheet1!" & Range(nradd).Offset(1, 0).Address
End If
End Sub

 

[wbstadeli]

Thanks for your reply Roderick,

Its getting a run time error '1004' (Method 'Range' of object_worksheet failed) on this line. Any suggestions?

nradd = Sheet1.Range("TimeLog").AddressLocal

 

[Roderick_E]

Thanks for your reply Roderick,

Its getting a run time error '1004' (Method 'Range' of object_worksheet failed) on this line. Any suggestions?

nradd = Sheet1.Range("TimeLog").AddressLocal

Hmmm -- is your named range called "TimeLog" and is it on Sheet1??? If not, please correct that.
If everything is correct, try: nradd = Sheet1.Range("TimeLog").address