Find Last Row in Worksheet Without VBA

ScottUlmer

New Member
Joined
Dec 13, 2016
Messages
29
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

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
Top