How to select a dynamic (?) range?

[HelenW]

Hello all

Hope someone can help with this - Please can I have some VB code that will select all rows in columns H2:M2, when I don't know each time where the last row will be? I'm guessing this is a dynamic range, but not sure!

Thank you

Helen

[arthurbr]

The macro recorder provided this :Sub Macro1()

Sub Macro1()
    Range("h2:m2").Select
    Range(Selection, Selection.End(xlDown)).Select
End Sub

[HelenW]

Perfect, thanks! Most note that one down somewhere for future reference...

[arthurbr]

It's mimicking this action: select the first cells and press the down arrow while holding down the ctrl+shift keys

[royUK]

You don't need to select ranges in VBA as a rule.

Dim rng As Range

Set rng = ActiveSheet.Range(Cells(2, 8), Cells(Rows.Count, 13).End(xlUp))
'e.g
rng.Copy

If the range has empty rows & columns around it you could use CurrentRegion

[HelenW]

Thank you for this - I haven't yet got my head round "dim" and all that though! Can you please advise what the numbers (2, 8 and 13) apply to?

[royUK]

The numbers are references to Row & column (R1C1), so Cells(1,1) is A1

[HelenW]

Wonderful - I'll give that a go, thanks. Do I need to use the rng.copy if I don't need to copy it? Just need to select the range.

[HelenW]

Sorry - it only selects the first 2 columns in the range! Any ideas?