Dynamic Macro Range

[rhmkrmi]

Hello,
How can I make a range in a macro dynamic? For example, in the macro below, how can I make it pick upnew rows, like P15, added to the range P1:P14?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim changed As Range,c As Range
Dim cVal
Const myR As String ="P1:P14" '<- Your range(s)

Set changed =Intersect(Target, Range(myR))

If Not changed IsNothing Then
Application.EnableEvents = False
For Each c In changed
cVal = c.Value
Select Case True
Case IsEmpty(cVal),IsNumeric(cVal), _
IsDate(cVal),IsError(cVal)
' Do nothing
Case Else
c.Value = UCase(cVal)
End Select
Next c
Application.EnableEvents = True
End If

End Sub

 

[MARK858]

Sub AddRow13()
    Rows("13:13").Offset(1).Insert Shift:=xlDown
End Sub