Select range until cell with certain text in Excel with VBA

Question

Trying to select header / first row and data under it from columns C to O Until the first cell in column C has the word "Estimated" I came up with this VBA code based on another answer on stackoverflow, but it doesn't seem to be working

All help is deeply appreciated!!

Range("C1").Select
For i = 1 To 9999
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = TRUE Then Exit For
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = FALSE Then Exit For
Next
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = TRUE Then             
    Range(Cells(ActiveCell.Row, 15), Cells(ActiveCell.Row, ActiveCell.Column + i - 1)).Select

`

why not use the Range.Find method? Also, you probably don't need to Select anything or use the Activate/ActiveCell (and it should generally be avoided). — David Zemens
– David Zemens, Commented Jul 12, 2018 at 20:58

user4039065 · Accepted Answer · 2018-07-12 21:01:46Z

1

Try,

range(cells(1, "C"), cells(application.match("Estimated", columns(3), 0)-1, "O")).select

Not sure from your narrative whether the row containing "Estimated" was to be included or not. The -1 tells it not to include the "Estimated" row.

answered Jul 12, 2018 at 21:01

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Daren S Over a year ago

Thanks for the response. it is supposed to exclude it. When I ran it, I got "type mismatch" as an error.

DisplayName · Accepted Answer · 2018-07-13 13:57:13Z

0

you could use

Range("O1", Columns(3).Find("Estimated", , xlValues, xlPart)).Select ' to include row with "Estimated" in column C

or

Range("O1", Columns(3).Find("Estimated", , xlValues, xlPart).offset(-1)).Select ' to exclude row with "Estimated" in column C

answered Jul 13, 2018 at 13:57

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

2 Comments

Daren S Over a year ago

Thanks for having both of them. I used the second one, but when I ran it, I got "object variable or with block variable not set" as an error.

DisplayName Over a year ago

Is “Estimated” possibly not there?

Collectives™ on Stack Overflow

Select range until cell with certain text in Excel with VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related