Range statemeny query

[Graham H]

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

 

[paul.robinson]

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

 

[Graham H]

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

 

[Dave Peterson]

Maybe J2:J10 was selected--but row 10 was hidden by the filtering. So you
couldn't see that hidden selected cell (J10).

 

[Graham H]

Column L and Column J each have the same heading so when r is run it returns 10 which is
correct for the data plus the header row in column J. Thus there is one header row and 9
rows of data, the 9 rows of data being returned by the range select statement but not
including the header row. I know I am probably being thick but I just couldn't see why it
returned that with r being 10. J2:J10 were selected it was just a range definition I was
not familiar with. Don't waste any more time on it, it works so I will leave it alone.
Many thanks.

Graham

 

[Graham H]

Sorry, definiately ignorant but in a different way. Yes, columns and row 10!!
Aplologies

Graham