Find Last Row in Worksheet Without VBA

[ScottUlmer]

Long story short, my company creates a headache when you make anything with VBA, and I know this is SUPER easy to do in VBA, but as mentioned before I need to do this without VBA.

I find the last row by doing:

B1: =LOOKUP(2,1/(C:C<>""),ROW(C:C))

I find the last column by doing:

B11: =COLUMN(INDEX(1:22,,LOOKUP(2,1/(22:22<>""),COLUMN(1:22))))

Now this works, but all of those "22"s are the hard-coded number from the what the last row is. I need to be able to replace all of the "22"s to be dynamic.

I have tried to do value(b1), sum(b1), and indirect version of b1, everything I could think of. Is there anyway to return the value of b1 and place it in the b11 formula? I even tried to nest my code in it, but it did not work. I think it has to do with the ":" in the ranges, so I tried all of those combinations with it to include #:code, #&":"&code, (#)&":"&(code). Nothing so far has worked. I can get formulas to pull the value of b1, but not if they have a ":" in the same section as it. Any advice would be appreciated.

 

[hiker95]

ScottUlmer,

See if the following will help you:

Last Row Formula

https://www.mrexcel.com/forum/excel-questions/67536-last-row-data-formula.html