Table Manipulation – ListObject First Blank Row

Returns the index of the first blank row in an Excel.ListObject within a specified column range.

This function checks each row within the data body of the table, scanning the specified column range. If all cells in a row are empty, that row is considered the first blank row.

    Function xlListObjectFirstBlankRow(ListObject As Excel.ListObject, 
                                       StartRow As Integer, 
                                       StartColumn As Integer, 
                                       EndColumn As Integer) As Integer

        If ListObject Is Nothing Then Return 0

        Dim iRow As Integer
        Dim rowCount As Integer
        Dim iRowIndex As Integer, iColIndex As Integer
        Dim currentRowValue As String

        Try
            rowCount = ListObject.DataBodyRange.Rows.Count

            'Check il all the cell fron StartCol to EndCol are empty
            For iRowIndex = StartRow To rowCount
                Dim bIsEmpty As Boolean : bIsEmpty = True
                For iColIndex = StartColumn To EndColumn
                    currentRowValue = CStr(ListObject.DataBodyRange.Cells(iRowIndex, iColIndex).Value)
                    bIsEmpty = String.IsNullOrEmpty(currentRowValue) And bIsEmpty
                Next

                If bIsEmpty Then
                    iRow = iRowIndex
                    Exit For
                End If
            Next
        Catch ex As Exception
            Return 0
        End Try

        Return iRow

    End Function

    Function xlListObjectFirstBlankRow(ListObject As Excel.ListObject, 
                                       StartRow As Integer, 
                                       StartColumn As Integer, 
                                       EndColumn As Integer) As Integer

        If ListObject Is Nothing Then Return 0

        Dim iRow As Integer
        Dim rowCount As Integer
        Dim iRowIndex As Integer, iColIndex As Integer
        Dim currentRowValue As String

        Try
            rowCount = ListObject.DataBodyRange.Rows.Count

            'Check il all the cell fron StartCol to EndCol are empty
            For iRowIndex = StartRow To rowCount
                Dim bIsEmpty As Boolean : bIsEmpty = True
                For iColIndex = StartColumn To EndColumn
                    currentRowValue = CStr(ListObject.DataBodyRange.Cells(iRowIndex, iColIndex).Value)
                    bIsEmpty = String.IsNullOrEmpty(currentRowValue) And bIsEmpty
                Next

                If bIsEmpty Then
                    iRow = iRowIndex
                    Exit For
                End If
            Next
        Catch ex As Exception
            Return 0
        End Try

        Return iRow

    End Function

Posted by ipercube