Range statemeny query
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 |
Range statemeny query
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 |
Range statemeny query
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 |
Range statemeny query
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 |
Range statemeny query
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 |
All times are GMT +1. The time now is 06:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com