Paul,
I am hesitant to disagree but I ran the following part procedure

Dim r As Integer
Dim ws1 As Worksheet
Set ws1 = Sheets("Sheet1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row
MsgBox r
Range("j2:J" & r).Select

and I can assure you that r was returned as 10 in the message box and the range selected
was J2:J9.
It could be me missing something simple which would not be unusual.

Graham

wrote:
Hi
If r = 10 this will give you Range("J2:J10"). So r MUST be 9.
The question is now "Why is r = 9"?

regards
Paul

On Jul 3, 11:18 am, Graham H wrote:
Within I procedure I have come across a range statment which I cannot work out how it does
what it does and I would really like to understnd it.

Range("J2:J" & r)

If for example the value of r is 10, this range will return the range J2:J9, which is what
it is supposed to do, I know, but is 9 cells and I cannot see how it arrives at this.
They say he who asks a question is only ignorant for as long as it takes to hear the
answer to the question but he who doesn't ask a question is ignorant forever. Well I admit
to being ignorant at the moment but I would be grateful if someone could alleviate this
condition.

Graham