Expanding a Selected Range

[bluelabel]

Hi VBAers,

I have a range which is selected via a find function, not a cell reference.

The find looks for two unique strings then selects the range between.

From here I want the selected range to expand out 12 columns, say from Column A to Column M and keep that range selected.

I can't reference a cell range as the location of the selection strings vary day to day.

Here is what I have so far:

    Range("A:A").Find(What:="Point 1").Offset(1, 0).Select
    Range(Selection, Range("A:A").Find(What:="Point 2")).Select

How do I get this range to include everything out to Column M please?

 

[Peter_SSs]

How do I get this range to include everything out to Column M please?

Change your second line of code to

Range(Selection, Range("A:A").Find(What:="Point 2")).Resize(, 13).Select

.. but note that you rarely need to actually select to work in vba and selecting slows your code considerably. You can probably do what you need without actually selecting that range.
However, without knowing exactly what you are going to do with the range, I can't suggest what the code would be.

 

[bluelabel]

Change your second line of code to

Range(Selection, Range("A:A").Find(What:="Point 2")).Resize(, 13).Select

.. but note that you rarely need to actually select to work in vba and selecting slows your code considerably. You can probably do what you need without actually selecting that range.
However, without knowing exactly what you are going to do with the range, I can't suggest what the code would be.

Thank you Peter, that worked a treat.

I have only ever used .select as I don't know anything else to use and the code usually errors out if I don't

 

[Peter_SSs]

Thank you Peter, that worked a treat.

Good news. You're welcome.

I have only ever used .select as I don't know anything else to use and the code usually errors out if I don't

Well, suppose that you wanted to copy that region and paste it starting at cell Z1. Your code would now be

Range(Selection, Range("A:A").Find(What:="Point 2")).Resize(, 13).Select
Selection.Copy Destination:=Range("Z1")

you can just leave out both bits of red code and put that all in one line:

Range(Selection, Range("A:A").Find(What:="Point 2")).Resize(, 13).Copy Destination:=Range("Z1")

The copy/paste will have been done without selecting the extra rows & columns.
Of course the earlier selection of "Point 1" could also be eliminated but I was just demonstrating one example here.