Name a Dynamic used range that refreshes when new data is added

[Martin sherk]

I am trying to create a name called "MyData" for a used range in sheet 1, that used range changes a lot due to users adding new rows and columns or even if they delete data in that sheet and add new data .. i want the VBA code to be able to name that new range or data automatically to "MyData" no matter what.

How can I do that, I literally tried everything it either leads to an error message or I have to manually run the code every time, it's really driving me crazy.

My data is in sheet 1 is dynamic and it starts from cell A1.

i really appreciate if anyone can help me with that.

 

[jdellasala]

You could use a LAMBDA function instead of VBA.
I posted this LAMBDA function a while back (see this post for the corrected formula!). What I created it for is data that an Add-in can retrieve in a 2 column format - Date and Value. However it is dynamic depending on date, frequency (monthly, etc.) and other factors that change how many columns of data. I call the function _FredRange. It automatically determines how many rows of data there are, and then creates a 2 column array of that many rows with the data in those rows and columns. The only required parameter is the top left cell of the 2 column array. More columns can be optionally specified.
So you end up with a cell with a formula like this:

=_FredRange(A2)

Say that formula is in cell D2. You can now create a Named Range (let's call it Fred) by specifying the formula

=Sheet7!$D$2#

as the Refers to part of the range. That will give you that dynamic 2 column array, but then simply use

=INDEX(Fred,,1)

for the named range FredDt for the first column, and

=INDEX(Fred,,2)

for the named range FredVal. (That's what I use for this particular data.
You now have access to three Range Names that refer to data that can dynamically grow or shrink as the data changes.
The LAMBDA function is easily used in any workbook using the Advanced Formula Environment add-in, or through an easy to record Macro in your Personal Workbook.
See what you think about that.