3

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

Valip
  • 4,440
  • 19
  • 79
  • 150
  • The error message just means you passed the `CountA` function an incorrect argument. I believe it expects a valid `Excel.Range`. If it's the ninth column I think it should be `wsConfig.Columns("I:I")` that you pass to `CountA` but I'm guessing as I don't have a project to hand. – Equalsk Mar 09 '17 at 13:20
  • Oh, I think if you look here the accepted answer might be what you want: http://stackoverflow.com/questions/5411355/how-do-i-get-an-entire-column-in-used-range – Equalsk Mar 09 '17 at 13:22
  • I tried that but it returns 51 (total number of rows). – Valip Mar 09 '17 at 13:28
  • Maybe it also counts the cells whose background color is different? – Valip Mar 09 '17 at 13:29
  • `CountA` won't get the last row if there are empty cells *between* other cells in the column you are looking at. Check this question: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Robin Mackenzie Mar 09 '17 at 13:31
  • @RobinMackenzie there won't be empty cells between other cells in the column. I checked that question I and think that the code from "Find Last Row in a Column" could help me, but I don't know how to convert VB to C#... – Valip Mar 09 '17 at 13:36
  • I think the code you posted in your edit is on the correct track. Try: `var usedRows = wsConfig.Range["I" + wsConfig.Rows.Count].End(Excel.XlDirection.xlUp).Row;` And maybe you will need to cast `wsConfig.Rows.Count.ToString()` – Robin Mackenzie Mar 09 '17 at 13:42
  • Tried it, and I get `usedRows = 1048576` – Valip Mar 09 '17 at 13:44
  • With `xlUp` not `xlDown` ? – Robin Mackenzie Mar 09 '17 at 13:45
  • Sorry, I used `xlDown` ... it works after changing it to `xlUp`! You should post the solution as an answer. Thank you! – Valip Mar 09 '17 at 13:46
  • No problem - I posted an answer. Feel free to accept if it helped ! – Robin Mackenzie Mar 09 '17 at 14:37

2 Answers2

2

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;
Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56
1

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]
sirdoug9
  • 94
  • 5