mortgageman
Well-known Member
- Joined
- Jun 30, 2005
- Messages
- 2,015
Sometimes there is a method to my madness. Thanks to the help I got on the board this morning, I was able to solve a problem that was bugging me for a while. Whenever I got a new biweekly client, I had to add the name to my client named range. This required me to delete the range name, and then create it again (after I added the new clients name). With the help I got this morning (from Jaafar and Oaktree) in knowing the last row of a range, I was able to automate the whole process. Here is the code if anyone would like it for their own use. (You will need to change the range to suit of course). BTW, it is only fair to mention that my ability to write a Worksheet Change event came from this board as well!
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim last_cell
With Range("name1")
last_cell = Right(.Address(True, False), Len(.Address(True, False)) - _
InStr(1, .Address(True, False), ":"))
last_cell = Val(Right(last_cell, InStr(1, last_cell, "$")))
End With
If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row = (last_cell + 1) Then
ActiveWorkbook.Names("name1").Delete
ActiveWorkbook.Names.Add Name:="name1", RefersToR1C1:="=Sheet1!R1C1:R" _
& (last_cell + 1) & "C4"
End If
End Sub
Gene, "The Mortgage Man", Klein
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim last_cell
With Range("name1")
last_cell = Right(.Address(True, False), Len(.Address(True, False)) - _
InStr(1, .Address(True, False), ":"))
last_cell = Val(Right(last_cell, InStr(1, last_cell, "$")))
End With
If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row = (last_cell + 1) Then
ActiveWorkbook.Names("name1").Delete
ActiveWorkbook.Names.Add Name:="name1", RefersToR1C1:="=Sheet1!R1C1:R" _
& (last_cell + 1) & "C4"
End If
End Sub
Gene, "The Mortgage Man", Klein