Range Usage

[Guest]

I need to be able to adjust the ending cell range in this example by using a
formula,for when I do an insert rows, the K76 will become K86 or anyother
number.What are my options and how do I construct this statement so that I
will not have to touch is statement again.

If Not Application.Intersect(Target, Me.Range("K4:K76")) Is Nothing Then

 

[Rick Rothstein \(MVP - VB\)]

I need to be able to adjust the ending cell range in this example by using

a
formula,for when I do an insert rows, the K76 will become K86 or anyother
number.What are my options and how do I construct this statement so that I
will not have to touch is statement again.

If Not Application.Intersect(Target, Me.Range("K4:K76")) Is Nothing Then

Put the 76 or 86 or whatever into a String variable and concatenate it into
the range text...

Dim ColNumber As String
.....
.....
ColNumber = "86"
.....
.....
If Not Application.Intersect(Target, Me.Range("K4:K" & ColNumber)) Is
Nothing Then
.....
.....

Rick

 

[Guest]

Thanks will give it a try.

Put the 76 or 86 or whatever into a String variable and concatenate it into
the range text...

Dim ColNumber As String
.....
.....
ColNumber = "86"
.....
.....
If Not Application.Intersect(Target, Me.Range("K4:K" & ColNumber)) Is
Nothing Then
.....
.....

Rick