CODEWITHSUNDEEP
c#excelinterop
Valip
Valip

Reputation: 4610

C# Interop get last row with data from a specific column

I'm trying to get the last row with data from a specific column and I'm getting the following error when running my code:

Unable to get the CountA property of the WorksheetFunction class

var xlApp = new Microsoft.Office.Interop.Excel.Application();
var wb = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
var wsConfig = wb.Sheets["Config"];

var usedRows = xlApp.WorksheetFunction.CountA(wsConfig.Columns[9]);

Also tried the following code, but returns:

Exception from HRESULT: 0x800A03EC

var usedRows = wsConfig.Range["I1", wsConfig.Cells[1, wsConfig.Rows.Count]].End(Excel.XlDirection.xlDown).Value;

Example:

Column I
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| |

=> usedRows = 5 because there are 5 rows with data on the column I

Upvotes: 3

Views: 5334

Answers (2)

sirdoug9
sirdoug9

Reputation: 104

I know this is a couple of years old but just thought I'd put this here. The accepted answer may require a bit of tweaking, as it didn't quite work for me. So I've modified it to the following: 

var usedRows = wsConfig.Range["I" + wsconfig.Rows.Count.ToString()].End[Excel.XlDirection.xlUp]

Upvotes: 2

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

The classic method to get the last used row in a column (e.g. A) is:

last = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row

Which says 'working from the last available row in the worksheet (changes from version to version) go upwards until you hit a cell with a value - your last row.

You can write this in in your example (for column I) with:

var usedRows = wsConfig.Range["I" + wsConfig.Rows.Count.ToString()].End(Excel.XlDirection.xlUp).Row;

Upvotes: 2

Related Questions