LASTUSEDCOLUMN

=LASTUSEDCOLUMN(0)

LASTUSEDCOLUMN returns the last column with an entry in the current sheet.

RicoS

Board Regular
Joined
May 1, 2019
Messages
62
LASTUSEDCOLUMN returns the last column with an entry in the current sheet.

Excel Formula:
=LAMBDA(colFrom,
        LET(c,IF(colFrom=0,16000,colFrom),
              i,MAX((MOD(c,10^{1;2;3})=0)*{1;2;3}),
              j,LOG(c),
              k,(j<4)*(j=INT(j)),
              x,0+(COUNTA(INDIRECT("C"&c&":C"&MIN(16384,c+(10^i)-1),FALSE))>0),
              return,IF((x*(i=0))=1,c,LASTUSEDCOLUMN(c+(x*(10^i))-(10^(i-(x*i)-(k*(x=0)))))),
        return)
)
 
Upvote 0
@RicoS
Any chance you would give some explanation of LASTUSEDCOLUMN and LASTUSEDROW
 
Hi! I'm really sorry, my notifications were going into spam and I haven't been on here for a month or two!

Yes, this function just recursively perform a COUNTA() on columns of data. Due to the memory limitations you can't simply check every single column or row and recursively loop through that until something is found, so I start by checking every 10,000 columns (16000-16384[max col], 15000-15999 etc. then if something is found in that range, we then loop through the hundreds to pinpoint further (if we find something in 7000-7999, we then check 7900-7999, then 7800-7899 etc) and then every 10 and so on:

For last column of 1756:
COUNTA(16,000:16,384) = 0
COUNTA(15,000:15,384) = 0
......
COUNTA(1,000:1,999) > 0
COUNTA(1,900:1,999) = 0
COUNTA(1,800:1,899) = 0
COUNTA(1,700:1,799) > 0
COUNTA(1,790:1,799) = 0
COUNTA(1,780:1,789) = 0
COUNTA(1,770:1,779) = 0
COUNTA(1,760:1,769) = 0
COUNTA(1,750:1,759) > 0
COUNTA(1,759:1,759) = 0
COUNTA(1,758:1,758) = 0
COUNTA(1,757:1,757) = 0
COUNTA(1,756:1,756) = 1

We start by passing a zero to the column from, and the first row of the function will change that to 16,000 as the max number of columns is 16,384. This will always be the starting point of the function.

the variable i then calculates which multiple of 10 the recursively passed column from is in (up to max of 3 because it can't be greater than 1,000 (unlike rows, which are 1,000,000, so require to check 1;2;3;4;5). It does this by calculating the maximum value in the array where the remainder of the value divided by 10^the array is equal to 0. For example, if colFrom is 8000, then MOD(8000,10^3)=0 is true, whereas if I have 8100 then MOD(8100,10^3)=0 is false but MOD(8100,10^2)=0 is true and thus i = 2.

the variable j returns the log (default log10) of the colFrom

variable k returns only if j is an exact number (otherwise returns zero). This determines if we're "stepping down" because we've reached the last combination in our multiple of 10. For example, when we search between 1,000 and 1,999 and find nothing then we know that the step is now 100 (10^2) rather than checking 1,000 columns.

variable x is the counta function, from the colFrom value and colFrom plus 10^ variable i (the multiple of 10), subtracting 1 (e.g. if colFrom is 2000, then i will be 3 and so colTo will be 2000 + (10^3) -1 = 2999.

Finally we either return the result or pass the new last column back to the function until we find something. If we both find something (i.e. x=1) and i is also 0 (not divisible by 10^1;2;3) then we return that result. If i is not zero, then that just means we've found something within say the 1000 range (e.g. COUNTA(1000:19999)>0 = TRUE), so we'd subtract 100 from the colFrom value and pass that back recursively as the new starting point (e.g. 1900, 1800 and so on). Only when we checked every thousand, hundred and ten columns can we then check single columns for the first (or last!) entry.

Overall, it means that you shouldn't run out of memory when performing this function as it should only ever have a maximum of 43 iterations to find the last column using this method. I'm sure I could have done it differently, but this was the geekiest method I could both think of and also have my brain cope with.
 
@RicoS

Thank you very much for your explanation even if it almost went over my head. I think I have a lot more to learn before asking such questions.

Hans K
 
Being next to the other function you posted, took a pick on this one also. First thing I've tried, on a blank sheet, entered "1" value in XFD1, called the function, returned 16999 instead of 16384.
Anyhow, this can be fixed with ease, I think, but what is the purpose of this function, to find isolated values on areas out of view on remote areas? For that we can clear whole sheet.
if the purpose of this is to find lost data misplaced on a spreadsheet that has data already, for example, tables or arrays that take some width of the horizontal real estate, and we want to keep it clean, then, it makes some sense. So, I have tried something simple on an empty sheet
In A3 : =SEQUENCE(,100)^0 and called the function,got NUM error. This is ok? . The one with last rows works the same? Probably it's only me , not getting the point.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCW
1=LASTUSEDCOLUMN(0)
2#NUM!
31111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
4
Sheet4
Cell Formulas
RangeFormula
A1A1=FORMULATEXT(A2)
A2A2=LASTUSEDCOLUMN(0)
A3:CV3A3=SEQUENCE(,100)^0
Dynamic array formulas.
 
this feature why when sequence 100 show error
 
Hi Steven, Rico​

The lightest, therefore the fastest and most basic argument free functions to solve it with today's "technology":
LC() Last Column, LR() Last Row.
Non recursive, use iterative REDUCE. Concept is very simple. We start with the entire last clm or row, we check for blanks, if all blanks we offset -1, check again and so on, if non blanks are found offsetting stops and we return the respective clm or row nr. (function of current iteration) repeatedly until the loop finishes. ( REDUCE being non recursive does not have exiting condition, so must finish all iterations but IF(v,v,..calc...) finishes all the remaining loops very fast when v takes a value, since the main "slow" scanning calculation gets overtaken)
Define these functions directly in the name manager as they are: (copy paste)
LC()
Excel Formula:
=LAMBDA(REDUCE(0,SEQUENCE(2^14)-1,LAMBDA(v,i,IF(v,v,IF(NOT(AND(ISBLANK(OFFSET(!$XFD:$XFD,,-i)))),2^14-i,0)))))
LR()
Excel Formula:
=LAMBDA(REDUCE(0,SEQUENCE(2^20)-1,LAMBDA(v,i,IF(v,v,IF(NOT(AND(ISBLANK(OFFSET(!$1048576:$1048576,-i,0)))),2^20-i,0)))))
Notes:
- To scan the entire spreadsheet (when last clm or row is closest to top left corner), longer it takes, every iteration checks for blanks a lot of cells, many times.
LR() when last row is 8 takes approx. 4 min, and LC() when last clm 8 takes double, approx. 8 min. It takes longer to check 1048576 cells 16384 times than to check 16384 cells 1048576 times.
- A tone of functions can be written easily based on this concept, to scan only sections or areas of spreadsheet, other directions, top to bottom, left to right, extracting last row of last column value and last clm of last row, intersections, functions for scaning other spreadsheets or workbooks, everything based on same simple concept. Take into consideration that any added calculation or operation in an iterative process can slow things down when so many iterations are involved.
- When the last cell is the very cell where you call the function, circular reference will ocurre so you need to activate file-options-formulas-enable iterative...
- Functions are volatile, (OFFSET) so after you use them (one at a time) it's better to put them in edit mode (add a space before =) to keep them from recalculating.
As easter eggs finder functions are pretty good. ✌️😉
LCLR.xlsx
XEWXEXXEYXEZXFAXFBXFCXFD
4
5
6
7
8instant
9when last clm close to right side
10
11=LC()
1216381X
13
14
15
Sheet2
Cell Formulas
RangeFormula
XEY11XEY11=FORMULATEXT(XEY12)
XEY12XEY12=LC()
Lambda Functions
NameFormula
LC=LAMBDA(REDUCE(0,SEQUENCE(2^14)-1,LAMBDA(v,i,IF(v,v,IF(NOT(AND(ISBLANK(OFFSET(!$XFD:$XFD,,-i)))),2^14-i,0)))))
LR=LAMBDA(REDUCE(0,SEQUENCE(2^20)-1,LAMBDA(v,i,IF(v,v,IF(NOT(AND(ISBLANK(OFFSET(!$1048576:$1048576,-i,0)))),2^20-i,0)))))


LCLR.xlsx
ABCDEF
1048560
1048561instant
1048562when last row close to the bottom
1048563=LR()
10485641048566
1048565
1048566X
1048567
1048568
1048569
1048570
1048571
1048572
1048573
1048574
1048575
1048576
Sheet2
Cell Formulas
RangeFormula
C1048563C1048563=FORMULATEXT(C1048564)
C1048564C1048564=LR()


LCLR.xlsx
XCOXCPXCQXCRXCSXCTXCUXCVXCWXCXXCY
1048431
1048432
1048433
1048434
1048435=LR()=LC()
10484361048441intersect16325X
1048437=ADDRESS(XCQ1048436,XCU1048436)
1048438$XCW$1048441
1048439
1048440
1048441X
1048442
1048443
1048444
1048445
Sheet1
Cell Formulas
RangeFormula
XCQ1048435,XCS1048437,XCU1048435XCQ1048435=FORMULATEXT(XCQ1048436)
XCQ1048436XCQ1048436=LR()
XCU1048436XCU1048436=LC()
XCS1048438XCS1048438=ADDRESS(XCQ1048436,XCU1048436)
 
Deep!! Everything takes time unless you are a photon!! 😂
 
Asked the photon for a faster method 😊
its take time
Try these ones:
LC()
Excel Formula:
=LAMBDA(REDUCE(0,SEQUENCE(2^14)-1,LAMBDA(v,i,IF(v,v,IF(COUNTIF(OFFSET(!$XFD:$XFD,,-i),"<>"),2^14-i,0)))))
LR()
Excel Formula:
=LAMBDA(REDUCE(0,SEQUENCE(2^20)-1,LAMBDA(v,i,IF(v,v,IF(COUNTIF(OFFSET(!$1048576:$1048576,-i,0),"<>"),2^20-i,0)))))
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top