Selecting dynamic range

0
votes

Is it possible to have excel dynamic select cells? I'd love to know how to make a macro that does that. I want it to select certain cells based on the value of other cells. For example, ik want to select A1 to A20. And ik want to do that by simply typing 1 in a cell and 20 in another.

I tried this code in vba

Dim example as range Set example = range ("A1:A20")

Example. Select.

It works, but i want the A1 and A20 to be changeable so i can select different ranges

Is that possible? And if not, what is the closest thing to it? Many thanks!

2
excelvba
Just above cell A1 there's a Name Box which displays the address of the currently selected cell. If you type A1:A20 in there it will select those cells, similarly if you have a named range and enter the name in that box it will select that range of cells. – Darren Bartrup-Cook

2 Answers

1
votes

I assume start row is in B1 and end row is in B2.

Set example = Range(Cells(Cells(1, 2).Value, 1), Cells(Cells(2, 2).Value, 1))
0
votes

It sounds like you should have two cells in that sheet that are your controls. So you would put "A1" in one cell as the starting cell, and "A20" as the ending cell in another. Let's just put them in C1 and D1 respectively.

Option Explicit
Sub MoveDataRange()
    Dim startCell As String, endCell As String
    startCell = Range("C1").Value
    endCell = Range("D1").Value
    Range(startCell, endCell).Select
    ...
End Sub

If you want the values in C1 and D1 to be dynamic, you would have to put a formula in there to do that. If you're ok with it being changed manually, I would just highlight them a different color to call attention to the macro dependency.