C# – Get last non-empty cell in Excel column

cexcel

How can we get a number/index of the last empty cell in a column in Excel.

I tried CountA and several others but it returns the count of non-empty cells or suppose there is a cell in the middle of nowhere after actual data with some spaces in that, the UsedRange returns range up to that cell.

What I need is a last non-empty cell in a column where that column may have empty cells in between. Using this last empty cell in that column, I can specify the last row to be considered for use.

Best Answer

In VBA you can use something like this on macro code:

rcount = Range("A:A").End(xlUp).Row

It will return the value of the last non-empty cell on the given column

In C# you can try:

Excel.Worksheet sh;
long lastRow;
long fullRow;

sh = app.Workbooks.get_Item("Workbook1.xlsx").Worksheets.get_Item("Sheet1");
fullRow = sh.Rows.Count;
lastRow = sh.Cells[fullRow, 1].get_End(Excel.XlDirection.xlUp).Row; 
//use get_End instead of End

Related Solutions

Excel – Last non-empty cell in a column

Using following simple formula is much faster

=LOOKUP(2,1/(A:A<>""),A:A)

For Excel 2003:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

It gives you following advantages:

it's not array formula
it's not volatile formula

Explanation:

(A:A<>"") returns array {TRUE,TRUE,..,FALSE,..}
1/(A:A<>"") modifies this array to {1,1,..,#DIV/0!,..}.
Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range (in our case {1,1,..,#DIV/0!,..}) that is less than or equal to the value (in our case 2), formula finds last 1 in array and returns corresponding value from result_range (third parameter - A:A).

Also little note - above formula doesn't take into account cells with errors (you can see it only if last non empty cell has error). If you want to take them into account, use:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

image below shows the difference:

enter image description here

Excel – Detect non empty last cell location using Excel VBA

Public Function GetLastCell(ByVal startRng as Range) as Range

    With startRng
        Set GetLastCell = IIf(.Offset(1).Value = "", .Offset(0), .End(xlDown))
    End With

End Function

For your example, you can define a Range variable rng, and call the above function in this way:

Dim rng as Range
Set rng = GetLastCell( Range("A1") )

Then rng is referring to the last cell of Column A

Best Answer

Related Solutions

Excel – Last non-empty cell in a column

Excel – Detect non empty last cell location using Excel VBA

Related Topic