CODEWITHSUNDEEP
vb.netexcelvisual-studioworksheet
Chrisetiquette
Chrisetiquette

Reputation: 321

How do I get an Excel cell name in vb.net?

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

Upvotes: 0

Views: 2443

Answers (1)

Yasha
Yasha

Reputation: 11

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

Upvotes: 1

Related Questions