Last used Row and Column Number in a Sheet

[Mrinal]

Hi,
I want a formula to get the maximum Row and column number used in any sheet. Please note that data is scattered in the sheet. Any formula which will give me the Row number and column number will solve my problem.

Thanks.
Mrinal

 

[Mrinal]

I want a formula alternate to END+HOME. This gives me the last row/column used in a work sheet.

 

[Mrinal]

Please someone reply.

 

[niceguy_jay]

what you can do is build a button and assign the following VBA code to that button

Sub last()

i = ActiveCell.SpecialCells(xlLastCell).Row
j = ActiveCell.SpecialCells(xlLastCell).Column

MsgBox (i & "," & j)

End Sub


on clicking the button will give you the last row and last column in a messagebox

 

[Mrinal]

Thank you for the reply. Is there any formula without using vba code?

 

[niceguy_jay]

I am afraid that there is no formula that can do this...you can make your custom function and use it but that is as good as the VBA code that I have provided above...

 

[GTO]

Are you not allowed to use vba, or just uncomfortable w/it?

 

[Mrinal]

I am afraid that there is no formula that can do this...you can make your custom function and use it but that is as good as the VBA code that I have provided above...

As VBA Coded Excel file extension is not same as xlsx, Now a days I am not attaching VBA code with my Excel file like previous version. I have made a Addin with your VBA code so that it will be common to every sheet but I am unable to activate that function last(). Can you please guide me in this matter so that I can use in the menu bar.

 

[Mrinal]

Are you not allowed to use vba, or just uncomfortable w/it?

I am very much fond of VBA code, I used to write till the previous version of xls file, now a days you can not make xlsx file with vba code attached what I understand, that is why I am avoiding using vba code for portability purpose.

Anyway, if you also can guide me how to use any addin in the menu bar it is welcome.

 

[GTO]

I am very much fond of VBA code, I used to write till the previous version of xls file, now a days you can not make xlsx file with vba code attached what I understand, that is why I am avoiding using vba code for portability purpose.

Anyway, if you also can guide me how to use any addin in the menu bar it is welcome.

Only two points or questions at this time, as I really should be sleeping. (I am sure that you will get great help here before I get to check back, but I will, just in case.)

You obviously are using the 'last cell' for a purpose. What is it?

If the users are hesitant to enable a xlsm macro, why do we think the will allow addins to load? Plus there is distribution...