Variable Cell Selection Based on Text Value in Cell

[wguidry]

Hey smart people. I have a column "A" of data and I want to select any cell in the column and run a macro that will select the next "x" number of cells below the current cell until I get to the cell that contains the word "Stop". Then I want the macro to start at the cell located 4 cells below that one and do the same thing again.

In this case "x" can be any number from 2 to perhaps 100. Can someone share some VBA code that can do this in Excel?

Thanks for your kindness.

EXAMPLE:

5
6
7
Stop
5
6
6
67
7
7
Stop
4
4
5
5
6
6
67
7
7
Stop

 

[Fishboy]

Hey smart people. I have a column "A" of data and I want to select any cell in the column and run a macro that will select the next "x" number of cells below the current cell until I get to the cell that contains the word "Stop". Then I want the macro to start at the cell located 4 cells below that one and do the same thing again.

In this case "x" can be any number from 2 to perhaps 100. Can someone share some VBA code that can do this in Excel?

Thanks for your kindness.

EXAMPLE:

5
6
7
Stop
5
6
6
67
7
7
Stop
4
4
5
5
6
6
67
7
7
Stop

Hi wguidry,

Can you please clarify whether you want the cell containing "Stop" to be included in the selection, or to stop at the cell before it?

Also, can you explain the logic in the next part? Will it always be 4 cells below the "Stop" cell? Is there a reason it doesn't start at the next numeric value after the "Stop" cell?

 

[wguidry]

Thanks,

//This works: //
Sub Selector()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveCell
Set rng2 = ActiveWorkbook.Worksheets("Sheet1").Columns(1).Find(What:="Stop", After:=ActiveCell, SearchDirection:=xlNext)
Range(rng1, rng2).Select
End Sub