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

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

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

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 wrote:

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


--

Dave Peterson

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

Dave Peterson wrote:
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 wrote:
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

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

Graham

Graham H wrote:
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

Dave Peterson wrote:
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 wrote:
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