Variable Cell Selection Based on Text Value in Cell

wguidry

New Member
Joined
Apr 22, 2003
Messages
42
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

New Member
Joined
Apr 22, 2003
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,277
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top