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

 

[dmt32]

Hi,
If you want your code to apply to all rows in Column P then change this line

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

to this

Set changed = Intersect(Target, Me.Columns(16))

and see if this does what you want

Dave

 

[rhmkrmi]

Thanks Dave.

I actually want the code below to start in P1but go over P14 f users insert rows.

Const myR As String ="P1:P14" '<- Your range(s)

 

[MARK858]

Maybe one way...

Const myR As String =Range("P1:P" & Range("P" & Rows.count).End(xlUp).Row).Address

 

[dmt32]

Thanks Dave.

I actually want the code below to start in P1but go over P14 f users insert rows.

Const myR As String ="P1:P14" '<- Your range(s)

If you want a variable range then need to change from Const (fixed) statement to a variable

Hope Mark858 does not mind but I have amended his suggestion (where I suspect he posted in haste) which may be what you are trying to achieve.

Dim myR As String
myR = Range("P1:P" & Range("P" & Rows.Count).End(xlUp).Row).Address

Dave

 

[MARK858]

Hope Mark858 does not mind but I have amended his suggestion (where I suspect he posted in haste) which may be what you are trying to achieve.

Dave

No problem, it was just as I was leaving the house

 

[dmt32]

No problem, it was just as I was leaving the house

you are not alone, in my advancing years I have posted some real howlers!

Dave

 

[rhmkrmi]

Thank you MARK858 and dmt32 but it is getting applied to the whole column.
What I need is range P1:P14 and then as they insert rows in this range, P14 expands.
I have other data in P15 onward which should not be upper case.

Thanks again.

 

[MARK858]

Create a named range for P1:P14 then use the named range in the code.

 

[rhmkrmi]

Excellent.
Thank you.
Works perfectly.

Is there a macro that I can use on a button to add rows from a certain row? For example, when we click on the button, it always inserts a row after row 13?

Thanks