Get last non-empty cell in Excel column

Question

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.

isJustMe isJustMe · Accepted Answer · 2013-05-27T17:33:03

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

Get last non-empty cell in Excel column

1 Answers