VBA select cell with a specific value and all cells below until blank

I would like to use a macro for a new but similar document I get each week. In the document there is a section with a label that I want to select just that section. It is divided by a blank row above and a total and blank row below. The total number in row 718 equals the number of rows in that section, so I may be able to trim that number and use it as a variable. The number of rows changes each week.

I can start with Cells.find (What:="New SRs"... But I don't know how to select all the way to the blank row.

Thanks for any help offered!

Example:

413 New SRs
414 SR#
415 54365-1
416 54366-1
417 54367-1
418 54367-2
...
717 54703-1
718 Total Service: 303
719 *Blank*
720 Next Section

If the section always start in row 413, and you want column A,

Dim rng As Range
':
Set rng = Worksheets("some_sheet").Range("A413")
If Not IsEmpty(rng.Offset(1, 0).Value) Then Set rng = Range(rng, rng.End(xlDown))

Unfortunately the number of rows above changes every week as well. I believe I got a good answer already. I appreciate you, and will save your input in my bag of tricks!

Have a great day.

Originally Posted by OmahaJay

Unfortunately the number of rows above changes every week as well.

I am not sure how that affects the solution I posted but no problem, if hrlngrv's solution works for you then by all means use it.

Originally Posted by Rick Rothstein

I am not sure how that affects the solution I posted but no problem, if hrlngrv's solution works for you then by all means use it.

To clarify, your one line of code did work. The number of rows did not affect your code. I just need to tweak it to include columns A through S. Thanks again for your help.

Originally Posted by hrlngrv

If the section always start in row 413, and you want column A,

Dim rng As Range
':
Set rng = Worksheets("some_sheet").Range("A413")
If Not IsEmpty(rng.Offset(1, 0).Value) Then Set rng = Range(rng, rng.End(xlDown))

Unfortunately the number of rows above changes every week as well, so the section does not always start at 413. I believe I got a good answer already. I appreciate you, and will save your input in my bag of tricks!

Have a great day.

Assuming you are searching column A and that cell A1 is a header (that is, it is not a section label), then this single line of code will find the cell and select its section...

Range(Columns("A").Find("New SRs", [A1], xlValues, xlWhole, , xlNext, False, , False), Cells(Rows.Count, "A")).SpecialCells(xlConstants).Areas(1).Select

Note: You should include an error trap if it would be possible for the searched for label not to be in the column.

This is 99% what I need. A copy/paste into my code worked perfectly! Thank you so much. I will fiddle with it so I can select columns A through S. Shouldn't be too hard.

Thank you so much!

Originally Posted by Rick Rothstein

Assuming you are searching column A and that cell A1 is a header (that is, it is not a section label), then this single line of code will find the cell and select its section...

Range(Columns("A").Find("New SRs", [A1], xlValues, xlWhole, , xlNext, False, , False), Cells(Rows.Count, "A")).SpecialCells(xlConstants).Areas(1).Select

Note: You should include an error trap if it would be possible for the searched for label not to be in the column.

This was the code that worked. Apparently I'm "slightly" better at excel than I am at using this forum!

Give this a try (I show my addition in red)...

Range(Columns("A").Find("New SRs", [A1], xlValues, xlWhole, , xlNext, False, , False), Cells(Rows.Count, "A")).SpecialCells(xlConstants).Areas(1).Resize(,19).Select

Originally Posted by Rick Rothstein

Give this a try (I show my addition in red)...

Range(Columns("A").Find("New SRs", [A1], xlValues, xlWhole, , xlNext, False, , False), Cells(Rows.Count, "A")).SpecialCells(xlConstants).Areas(1).Resize(,19).Select

All hail Rick Rothstein! Thank you sir!