hello all,
Ive been trying to figure out how to find the next empty row in an excel spreadsheet.
My project is a VB project, written in Visual Studio 2010 Express.
What i need to do is open the spreadsheet (that is predefined) and find the next available row. (in column A through to S)
My code so far for this little part of my prject is:

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(giftDirectory & "\Register.xls")
xlworksheet = xlWorkBook.Worksheets(1)

So ive been trying for hours to select the next available row.
Using "Range" and "Cells" but nothing seems to work.

Something like this:

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

When i use "Selection" it puts a blue squiggly line under it.

and this:

Dim rng As Range
rng = ActiveCell.SpecialCells(xlLastCell)

The same as "Selection". I get a blue line for "ActiveCell"

Doesn't work.

Can anybody give me an explanation? or guide me as to why i cant do this?

Edited 4 Years Ago by Gobble45: Clarifying..

I have always had bad luck with looking for empty rows. I have always made direct references to the data are.

I would normally wrap the code in two for statements like:

For i = 1 To NumberOfColumns
    For i = FirstDataRow To LastDataRow
    'Place your extraction code here.'
    Next
Next

I haven't tried this in VB, but in VBA you can directly Range("A1").End(xlDown).Select
I'm guessing you could use it as xlworksheet.Range("A1").End(xlDown).Select

Edited 4 Years Ago by adam_k: correction

Ive decided to use an Excel spreadsheet with a VBA macro, rather that using VB.

@adam_k Your solution worked a charm, thankyou :)

@Begginnerdev, thankyou for the help, i didnt end up trying your solution.

If Sheet is a reference to the worksheet in question then

Dim row As Integer = 1

Do Until IsNothing(Sheet.Cells(row,1).Value)
    row += 1
Loop

Edited 4 Years Ago by Reverend Jim: typo

This article has been dead for over six months. Start a new discussion instead.