Finding the last nonblank cell in a column in VBA or VSTO

[Bob Sullentrup]

Folks,

In VBA I have frequently used the formula:

last_row = ActiveWorkbook.Sheets("Sheet1").Range("A65534").End(xlUp).Row

to find the last cell with data in it in column A of Sheet1.

In Visual Studio Tools for Office (C#) the equivalent expression I have just
discovered is:

Sheet1.get_Range("A65534", Type.Missing).get_End(Excel.XlDirection.xlUp).Row

--
Bob Sullentrup

[William Benson]

Your method would not work if the last cell used was on row 65534, 65535, or
65536. Even if you corrected your typo (should have said 65536), if the data
extended to row 65536, you'd end either on Row 1 or on the last occurrence
of a break in the data, using End(xlUP). And what if there is no data in Col
A ... or what if the data in colums other than A extended below rows which
Col A's data held? There are a lot of other ways to get last row. The
quickest I know is:
Activesheet.UsedRange.SpecialCells(xlcelltypelastcell).Row



"Bob Sullentrup" <[email protected]> wrote in message
news:[email protected]...
> Folks,
>
> In VBA I have frequently used the formula:
>
> last_row = ActiveWorkbook.Sheets("Sheet1").Range("A65534").End(xlUp).Row
>
> to find the last cell with data in it in column A of Sheet1.
>
> In Visual Studio Tools for Office (C#) the equivalent expression I have
> just
> discovered is:
>
> Sheet1.get_Range("A65534",
> Type.Missing).get_End(Excel.XlDirection.xlUp).Row
>
> --
> Bob Sullentrup