asked on

Excel VBA: display last used column & last used row & initial / end range

Hello experts,

I am looking for the best way to built 3 procedures:

1-Display last used column letter in activesheet
2-Display last used row number in activesheet
3-Display initial Range and end range of activesheet example: A1:C4000

Thank you very much for your help.

SOLUTION

Sam Jacobs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Hakan Yılmaz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you, what about the other ways with Cells.SpecialCells, Cells.Find?

Hakan Yılmaz

I used Cells.Find method in my code.
Excel has it's own UsedRange definition. Which is always starting with A1, and ending with the lower/right cell which has any setting applied. It may be a value, a data validation, a comment, a fill format, a font size etc.
You may find the LastCell according to this definition by using the code below.

Cells.SpecialCells(xlCellTypeLastCell).Address

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much for those proposal.

Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column

Open in new window

Allows to get last used column number however if I want to get the column letter, what is the straightforward to proceed?

Excel VBA: display last used column &amp; last used row &amp; initial / end range

Excel VBA: display last used column & last used row & initial / end range