Row Scanning – Return Last Not Empty Cell Value (Left to Right)

Scans a row in an Excel.Worksheet from left to right and returns the value of the last non-empty cell before the first empty cell.

The function stops scanning when it encounters the first empty cell, returning the value of the cell immediately before it.

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

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

        Return String.Empty
    End Function

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

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

        Return String.Empty
    End Function

Posted by ipercube