Create a dynamic range, range(cells(), cells())

Question

I need to create a range of cells that will change for different persons names. For each person name I have the row and column numbers that will define the range, and this number are in some cells of the sheet.

For instance,

rows:

Cell(A5) = 3

Cell(A6) = 9

column:

Cell(A7) = 4 ----> (Column D)

so, the final range given by this set of rows and columns numbers is range("D3:D9"). For another person name I will have a different range given by some other numbers for rows and columns positions. The problem is that everyday new or existing people's names are added, so I cannot create a named range because it will change everyday. However, the values in cells A5/A6/A7 are capturing the new information for the location of the new range in different days.

I have tried to build this line of code, but it does not works:

Sub Dynamic_Range()

Dim rng As Range

Set rng = Range(Cells(Range("A5").Value, Range("A7").Value), Cells(Range("A6").Value, Range("A7").Value))

End Sub

Can someone advise on this?

Answer 1

Update

After I first answered this, it occurred to me that the offset function should do what you want, which will avoid VBA. In the same shared file, you should be able to see an example.

Original Answer

You probably can accomplish what you want by using a custom function. I say probably as it's unclear if your function/range is crossing sheets. Additionally, this approach uses a volatile function, which could causes some performance issues if it's a large file.

That being said, here's the syntax for a custom function to create a range based on some input parameters.

Function dynamicName(rowStart As Long, RowEnd As Long, theColumn As Long) As Range
    Application.Volatile
    Set dynamicName = Range(Cells(rowStart, theColumn), Cells(RowEnd, theColumn))
End Function

You can see in this sample file how it might work. If I used this function for cell F4

=SUM(dynamicName(HLOOKUP(F2,A4:B7,2,FALSE),HLOOKUP(F2,A4:B7,3,FALSE),HLOOKUP(F2,A4:B7,4,FALSE)))