Column Scanning – Return Last Not Empty Cell Value (Top to Bottom)

Scans a column in an Excel.Worksheet from top to bottom and returns the value of the last non-empty cell before the first empty cell.

The function iterates downward from the starting row and stops when it encounters the first empty cell, returning the value from the cell directly above it.

    Public Function xlT2B_ReturnLastNotEmptyCellValue(wsSheet As Excel.Worksheet,
                                                ByRef RowNumber As Integer, 
                                                ByRef ColNumber As Integer) As String

        For iRowIndex As Integer = RowNumber To wsSheet.Rows.Count
            If CStr(wsSheet.Cells(iRowIndex, ColNumber).Value) = "" Then
                RowNumber = iRowIndex - 1
                Return CStr(wsSheet.Cells(RowNumber, ColNumber).Value)
            End If
        Next

        Return String.Empty
    End Function

    Public Function xlT2B_ReturnLastNotEmptyCellValue(wsSheet As Excel.Worksheet,
                                                ByRef RowNumber As Integer, 
                                                ByRef ColNumber As Integer) As String

        For iRowIndex As Integer = RowNumber To wsSheet.Rows.Count
            If CStr(wsSheet.Cells(iRowIndex, ColNumber).Value) = "" Then
                RowNumber = iRowIndex - 1
                Return CStr(wsSheet.Cells(RowNumber, ColNumber).Value)
            End If
        Next

        Return String.Empty
    End Function

Posted by ipercube