[2005] Excel- Finding last used cell in a column.

[Kraig K]

I want to find the last used cell in ANY given column in an Excel spreadsheet. The following code, and variations of it, seem to only report the last used row for the ENTIRE sheet, I don't want that.

lRow = oExcel.Range("A").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

I've tried messing with the range value and many other things, I always get the same. I know I can simulate a Ctrl-Up key sequence but that is not reliable (blank in between values). Column A may or may not be longer than B.

To summarize, I want the last used cell (row) in column A, do some things, then get the last used cell (row) in column B and do some things.

[AIS4U]

Kriag K:
Take a look at si's tutorial: http://www.vbforums.com/showthread.php?t=391665

I think it will help you.

[Kraig K]

Kriag K:
Take a look at si's tutorial: http://www.vbforums.com/showthread.php?t=391665

I think it will help you.

Unfortunately, I did read the "special cells" section and tried what was there with no luck. The UsedRange applies to the entire sheet, not just a column. The xlUp or xlDown is not reliable due to the possibility of blank cells in between data. And the third example I haven't got to work.

Remember, I want the last used cell in a particular column, not for the entire sheet. Maybe I could set the UsedRange to A1:A65536 but that seems really clunky. I was hoping the third method could be tweaked, it seems elegant, now if it would only check by column.

[stimbo]

Obviously you need to change the variables to suit your particular needs but this should work (or very similar). Put it in a loop to go through the cells. It may be worth starting at the end cell of the column you want to check and work backwards to find the last used cell.

Not as efficient as the other methods I'm sure but it might do for now until someone posts the proper solution.

'Use r and j to determine where to search:
If CType(oSht.Cells(r, j), Range).Value.ToString = Nothing Then
 
Then

[Kraig K]

Obviously you need to change the variables to suit your particular needs but this should work (or very similar). Put it in a loop to go through the cells. It may be worth starting at the end cell of the column you want to check and work backwards to find the last used cell.

Not as efficient as the other methods I'm sure but it might do for now until someone posts the proper solution.

'Use r and j to determine where to search:
If CType(oSht.Cells(r, j), Range).Value.ToString = Nothing Then
 
Then

This would work which would basically be the same as what I did in another app, select the last cell in a column, then do xlUp (simulate Ctrl-Up) then get the address of the active cell. I was hoping for a more "clean" way and when I started messing around with the SpecialCells I thought that was going to be it.

Looks like that may not work but thing is, I've seen other posts where people say to use that command, was hoping I was just missing it.

[stimbo]

I found a post by Rob

Excel last row post

I think this bit might be of interest. I could be completely wrong but the 1 here may refer to column A. So just adapt it from that. Does it work?

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(xlCellTypeLastCell).Row).Value.ToString)

EDIT::::

Should be this:

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString)

[Kraig K]

I found a post by Rob

Excel last row post

I think this bit might be of interest. I could be completely wrong but the 1 here may refer to column A. So just adapt it from that. Does it work?

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(xlCellTypeLastCell).Row).Value.ToString)

EDIT::::

Should be this:

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString)

I moved the 1 behind the special cells, since I'm looking for row not column and it throws a System.NullReferenceException. Then I tried it just as you had it, same thing. I'm toying with it now, it looks like it should work.

[Kraig K]

Did a Google search and actually led me to a post on these forums. Original Post

I made it VB friendly. All you'll have to do then is parse out the row number from the returned cell address.

Private Sub FindLastUsedCell()
        Dim aRow As Integer
        Dim aRange As Excel.Range
 
        'This finds the last used cell in column A that is guaranteed to be empty
        'Find the row number below the bottom-most occupied cell for the entire sheet
        aRow = oExcel.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
 
        'Loop from lowest cell up until first cell with a value
        While oExcel.Cells(aRow, 1).Value = ""
            aRow = aRow - 1
        End While
 
        'aRange is the last used cell in Column A
        aRange = oExcel.Cells(aRow, 1)
 
        'pick which style
        Debug.WriteLine(aRange.Address)
        Debug.WriteLine(aRange.Address(, , Excel.XlReferenceStyle.xlR1C1))
 
    End Sub

[Kraig K]

Replace the appropriate section in the above code to parse out the cell number.

Dim Position, Cell As Integer
 
'aRange is the last used cell below in Column A
        aRange = oExcel.Cells(aRow, 1)
        Position = InStr(2, aRange.Address(, , Excel.XlReferenceStyle.xlR1C1), "C")
        Cell = Mid(aRange.Address(, , Excel.XlReferenceStyle.xlR1C1), 2, (Position - 2))
        Debug.WriteLine(Cell)

[RobDog888]

Yes, SpecialCells does seem to have an issue returning the last used cell for the entire sheet instead of the specified range when specified.

I have found that UsedRange works best for situations like yours.

'Column A = 1
Dim iRow As Integer = oSht.UsedRange(65535, 1).End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row
MessageBox.Show("Last Used Row in Column (A): " & iRow.ToString, "Last Used Row", MessageBoxButtons.OK)