Select Down VBA

[xluserg]

Hi all


How do I select down from cell starting with 1 in a cell to highest number.

1
2
3
1
2
3
4
5
1
2
3
4
5
6
7

If I was in any cell with the number 1 how would I select only 1 to 5 or 1 to7 stopping at hishest number. Using VBA


Thanks in advance


Graham

 

[Rick Rothstein]

Your question is not clear... Which 1 to 5? Which 1 to 7?

 

[xluserg]

Hi Rick


Say it was any column lets say Col D, I'm in Cell D4 I want to select 1 to 5 D4:D8 useing VBA


Thanks for the reply

 

[xluserg]

Can anyone help.

 

[Rick Rothstein]

Say it was any column lets say Col D, I'm in Cell D4 I want to select 1 to 5 D4:D8 useing VBA

I don't think you realize how many different ways your question can be interpreted as. For the question you asked above...

Range("D4:D8").Select

But I don't think that is what you had in mind. Closer might be this (where ActiveCell would be the cell you are "in")...

ActiveCell.Resize(5).Select

However I think that might also not address what you had in mind when you told us your numbering scheme back in Message #1. Perhaps you want to select from the ActiveCell down to the cell before the cell with a 1 in it...

Range(ActiveCell, ActiveCell.EntireColumn.Find(1, ActiveCell.Offset(1)).Offset(-1)).Select

I can think of other ways to answer what you might have asked, but I'll wait to see if any of the above were right. If none were, please clarify how you know what the starting cell is and exactly how you know where to select down to from it.

 

[xluserg]

Hi Rick


What I did say was

starting with 1 in a cell to highest number

What I should of included was some higher numbers so if there is a higher number like 24, the code would select 1 to 24. or 1-3, 1-5, or 1-7


Your code works but stops at the next 1.


Thanks for your reply