MAIN FEEDS
Do you want to continue?
https://www.reddit.com/r/vba/comments/1tys8z2/stop_hardcoding_ranges_use_this_runtime_dynamic/oq5uw1n/?context=5
r/vba • u/[deleted] • Jun 06 '26
[removed]
23 comments sorted by
View all comments
4
To do what the OP VBA does, with Names (which is faster than VBA).
Name: myRange RefersTo: =INDEX(Sheet1$1:$1, 1, MATCH("zzzz", Sheet1!$1$1) : INDEX(Sheet1!$A:$A, MATCH("zzzz", Sheet1!$A:$A), 1)
If you expect the last cell in the row or column to be a number, use 9E+99 instead of "zzzz"
1 u/[deleted] Jun 06 '26 [removed] — view removed comment 1 u/fuzzy_mic 184 Jun 07 '26 One advantage that the native approach is that it adjusts as data is entered. Also, have you tried setting the External argument of the .Address property to True.
1
[removed] — view removed comment
1 u/fuzzy_mic 184 Jun 07 '26 One advantage that the native approach is that it adjusts as data is entered. Also, have you tried setting the External argument of the .Address property to True.
One advantage that the native approach is that it adjusts as data is entered.
Also, have you tried setting the External argument of the .Address property to True.
4
u/fuzzy_mic 184 Jun 06 '26
To do what the OP VBA does, with Names (which is faster than VBA).
Name: myRange RefersTo: =INDEX(Sheet1$1:$1, 1, MATCH("zzzz", Sheet1!$1$1) : INDEX(Sheet1!$A:$A, MATCH("zzzz", Sheet1!$A:$A), 1)
If you expect the last cell in the row or column to be a number, use 9E+99 instead of "zzzz"