First blank line in excel

Question

Is there a way to find the first available empty string in an excel sheet using vsto or C #? I was looking for a watch, but could not find anything related to it. Any help in this regard would be greatly appreciated.

Answer 1

If the application is your excel application, you can scroll through the lines starting at number 1 and look for the first empty one using the CountA function (it returns the number of non-empty cells for the range):

int rowIdx = 0; int notEmpty = 1; while (notEmpty > 0) { string aCellAddress = "A" + (++rowIdx).ToString(); Range row = App.get_Range(aCellAddress, aCellAddress).EntireRow; notEmpty = _App.WorksheetFunction.CountA(row); } 

When the while loop exits, rowIdx is the index of the first empty row.

CountA receives 29 additional optional arguments, so it’s possible that you should pass Missing.Value 29 times with earlier versions of the framework.

Answer 2

 Excel.Worksheet xlWorkSheet1 = (Excel.Worksheet)excelbk.Worksheets["Sheet1"]; xlRange = (Excel.Range)xlWorkSheet1.Cells[xlWorkSheet1.Rows.Count, 1]; long lastRow = (long)xlRange.get_End(Excel.XlDirection.xlUp).Row; Long newRow = lastRow + 1 

Code for this link: http://social.msdn.microsoft.com/Forums/pl/exceldev/thread/90384567-3338-4c56-bc6b-70db94d8cbcc

Answer 3

There is the Range.End or Range.get_End property, which in combination with the direction will give you the first empty cell in the range.

Take a look at this question for a possible solution: How to find the next blank line in an Excel worksheet using C # programmatically
Key bit:

 Excel.Worksheet xlWorkSheet1 = (Excel.Worksheet)excelbk.Worksheets["Sheet1"]; xlRange = (Excel.Range)xlWorkSheet1.Cells[xlWorkSheet1.Rows.Count, 1]; long lastRow = (long)xlRange.get_End(Excel.XlDirection.xlUp).Row; Long newRow = lastRow + 1 

But if you have a column that, as you know, will always have every cell filled to the last, then you can get the end down.