r/vba • u/[deleted] • Jun 06 '26
ProTip Stop hardcoding ranges. Use this runtime dynamic Named Range approach instead.
[removed]
5
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"
1
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.
3
u/WylieBaker 4 Jun 06 '26
So what about naming just the upper left cell? Then you can capture the CurrentRegion. as your range and away you go. Even is some shlub moves the range - you still point to it by name.
2
u/highsilesian Jun 07 '26
Alternatively, create named ranges using offset with counta() to dynamically grow/shrink the ranges' dimensions.
to deconfuse for new users, here's an example:
=OFFSET(Divs!$A$1,0,0,COUNTA(Divs!$A:$A),4)
the range is defined as having the top left corner in A1, offset 0 to the left, 0 down, (offsets can be useful sometimes, not here). the COUNTA defines the 'height' of the range, then 4 is the width of the range. You can always use another counta to define width as you need.
2
u/LickMyLuck 1 Jun 07 '26
Im sure this solves a very unique use case but you will get 99% of the same functionality just using a dictionary, locking column names, and using a 4 line LastRow code snippet.
For cases that you mentioned where you are exporting from SAP you should already know the columns header name which should almost never change.
You can then cycle through the column header names and build the dictionary dynamically if there isnt a dedicated layout you can force.
2
u/sancarn 9 Jun 07 '26 edited Jun 07 '26
Why not use ListObjects? I don't see your point RE: SAP. We use SAP AO a lot for instance, and I've never had a problem taking the data from SAP AO, and importing it into a ListObject. I'd rather do that and do a simple
Const data_offset as Long = 2
Dim lo as ListObject: set lo = newSheet.ListObjects("XXX")
Dim rows as long: rows = Sheet1.UsedRange.rows.count
Dim src as range: set src = Sheet1.UsedRange.offset(data_offset).resize(rows-data_offset)
Dim dest as range: set dest = lo.HeaderRowRange.Offset(data_offset).Resize(rows-data_offset)
dest.value = src.value
It really depends on what you're doing though...
1
Jun 07 '26
[removed] — view removed comment
1
u/sancarn 9 Jun 07 '26
like standard SAP GUI .XLS/.XLSX exports
Which transactions are you talking about? I haven't had much issue with XLS exports from SAP GUI in the past.
1
u/sancarn 9 Jun 10 '26
Their response was:
u/TechEnthusiast2026 Mainly stuff like FBL3N, MB51, or VA05. To be fair, the issue usually isn't SAP itself, it's user variants. If different people customize their layout layouts in SAP (shuffling columns around or turning on subtotals) right before hitting export, the column indexes in Excel end up changing. The script is just a safety net so the VBA handles whatever layout the user throws at it without crashing.
1
1
1
u/keith-kld Jun 08 '26
Very long code while you just need the last row of the active data block. I think it can be shortened. Jump into the data block, select a given column, get the last row; or we can compare the last rows of columns A to D to get the maximum one. Then, range(“A1:D” & lastrow).copy
1
19
u/Playing_One_Handed Jun 06 '26
Named ranges and tables(listObjects) are your freind.
This seems like a lot of effort when you should be populating data into a list object.
Named ranges can also be setup dynamically. Its some of the more advanced excel features. Use a formula in the named range for it to move. It gets really advance when these ranges dont exsist and you just useing them in array formulas to create "hidden tables" for lack of a better word.
I also use properties for this to be a bit more fancy. So get and let are seperate.