0

I need the name of the first empty cell in a column, for example "E15" or "A3" I've tried using worksheet.Cells.Name and worksheet.Rows.Name but I don't think that is the correct syntax... please help!

Here's my code 

 Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add(eXe)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")



    Dim eColumn As Excel.Range = xlWorkSheet.Range("E2:E15")
    Dim rCell As Excel.Range




    For Each rCell In eColumn
        If rCell Is Nothing Then
            Dim LastCell As String = xlWorkSheet.Rows.Name
            MsgBox(LastCell)
        End If
        MsgBox(rCell.Value)
    Next rCell

(update)

I used the following code and got $E$15, is there a way to get the address with out the "$" symbol?

For Each rCell In eColumn
        If rCell.Value = "" Then
            Dim LastCell As String = rCell.Cells.Address
            MsgBox(LastCell)
        End If
        MsgBox(rCell.Value)
    Next rCell
Chrisetiquette
  • 311
  • 1
  • 4
  • 22
  • 1
    Is `.Address` what you're looking for? – BruceWayne Jun 23 '16 at 18:20
  • Are you using vb.net or vba? From the top of my hat without trying it: since you are iterating over ever< cell you could use if(cell.value ="") and then cell.address – surfmuggle Jun 23 '16 at 18:21
  • Use the google luke: [relative-cell-address-needed-in-excel-vba](http://stackoverflow.com/questions/24933692/) also [find-and-select-first-blank-cell-in-column-b-with-excel-vba](http://stackoverflow.com/questions/25155751/) or [select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset](http://stackoverflow.com/questions/14957994/) – surfmuggle Jun 23 '16 at 18:56
  • 1
    why not use Replace and remove the $? – logixologist Jun 23 '16 at 20:31
  • 1
    Something like: MsgBox(LastCell.replace("$", "") – logixologist Jun 23 '16 at 20:33

1 Answers1

1

To get the name of the coloumn use:

Dim columnLetter As String = ColumnIndexToColumnLetter(85) ' returns CG
Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
    Dim div As Integer = colIndex
    Dim colLetter As String = String.Empty
    Dim modnum As Integer = 0

    While div > 0
        modnum = (div - 1) Mod 26
        colLetter = Chr(65 + modnum) & colLetter
        div = CInt((div - modnum) \ 26)
    End While

    Return colLetter
End Function

Thing that you already know how to get the row number you are looking for. Then just use

dim CellName as String = columnLetter & rownum ' the row number you need

This should omit any $ signs you don't want

The example is being taken from here

Regards, Iakov

Yasha
  • 11
  • 2