I've used the following to return the first blank cell in a column:
Pass the filename, the worksheet number and the column letter. It should
count down the cells until it hits the first blank cell. The number it
returns is the number used in the cell address ie. (X, #)
Function ReturnTheFirstBlankCellInTheColumn(strFilename As String,
intWorksheetNumber As Integer, _
strColumnLetter As String) As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlCell As Object
Dim lngNonBlanks As Long
ReturnTheFirstBlankCellInTheColumn = 0
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strFilename)
With xlApp
Set xlSheet = xlBook.Worksheets(intWorksheetNumber)
Set xlCell = xlSheet.Cells
With xlSheet
.Activate
lngNonBlanks = 0
For Each xlCell In .Range(strColumnLetter & ":" &
strColumnLetter)
'***This is where it looks for the blank cell
If xlCell.Value <> "" Then
'***This cell is not empty so move to the next cell
lngNonBlanks = lngNonBlanks + 1
Else
'***This cell is empty so return the cell number (X,
#)
lngNonBlanks = lngNonBlanks + 1
ReturnTheFirstBlankCellInTheColumn = lngNonBlanks
Exit For
End If
Next xlCell
End With
Set xlCell = Nothing
Set xlSheet = Nothing
End With
xlBook.Save
'***Release the objects
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function
