Excel电子表格检查哪一行包含数据

I have a vb.net application from which I open an excel spreadsheet that contains data. I copy all the data and insert it into sql server. I'm coming across a small issue with finding the last row. Here's how I've been doing it right now...

我有一个vb.net应用程序,我从中打开一个包含数据的excel电子表格。我复制所有数据并将其插入sql server。找到最后一行,我遇到了一个小问题。这就是我现在一直在做的事情......

Dim lastRow As Long = 0
lastRow = xlws.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row

This finds the last row for me but often times, the spreadsheet might contain data that is not relevant to what I'm trying to insert into my table - in this case it's a confidentiality statement at the last row of the spreadsheet. So what i'm trying to do is set the last row to whatever the last row of ACTUAL data is. This is what it looks like...

这找到了我的最后一行,但通常情况下,电子表格可能包含与我试图插入到我的表中的内容无关的数据 - 在这种情况下,它是电子表格最后一行的机密性声明。所以我要做的是将最后一行设置为ACTUAL数据的最后一行。这就是它的样子......

So in this case - i want the last row to be recognized to be row 11 rather than row 13. The thing is - the formatting of the report might be slightly different (for the confidentiality statement) so often times it might start in column A or B and be merged (possibly) or they might write it elsewhere.

所以在这种情况下 - 我希望最后一行被识别为第11行而不是第13行。事情是 - 报告的格式可能略有不同(对于机密性声明),所以它常常可能从A列开始或者B和(可能)合并或者他们可能将其写在别处。

Another thing is that Column A and B of data (ending at row 11) might sometimes not have a value. How should I go about something like this?

另一件事是数据的A列和B列(在第11行结束)有时可能没有值。我应该怎么做这样的事情?

EDIT: This is what I'm coming up with - Hate GoTo's but....

编辑:这就是我想出的 - 仇恨GoTo's但......

LastRowCheck:

  If CStr(excel.Cells(lastRow, 4).Value) = "" And CStr(excel.Cells(lastRow, 5).value) = "" And CStr(excel.Cells(lastRow, 6).value) = "" Then
                lastRow += -1
  goto LastRowCheck
  End If

3 个解决方案

#1

How about:

Sub TheTrueLastRow()
    Dim i As Long

    For i = 1 To Rows.Count
        If Cells(i, "B").Value = "" Or Cells(i, "E").Value = "" Then
            lastRow = i - 1
            Exit For
        End If
    Next i
    MsgBox lastRow
End Sub

#2

Maybe something like this:

也许是这样的:

Sub Test()

    MsgBox LastRow(ThisWorkbook.Worksheets(2))

End Sub

Public Function LastRow(wrkSht As Worksheet) As Long

    Dim rLastCell As Range
    Dim lLastCol As Long, lLastRow As Long
    Dim rCol As Range

    On Error Resume Next
    With wrkSht
        lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set rLastCell = .Cells(lLastRow, lLastCol)

        'Look at each column, if the last cell is merged then look up from there,
        'otherwise leave the last row as it is.
        For Each rCol In .Range(.Cells(rLastCell.Row, 1), rLastCell).Columns
            If rCol.MergeCells Then
                LastRow = rCol.End(xlUp).Row
                Exit For
            Else
                LastRow = rLastCell.Row
            End If
        Next rCol
    End With
    On Error GoTo 0

End Function

Edit: Just noticed, it will fail at this point (well, if the last column is shorter rather than the first two).

编辑:刚注意到,此时它将失败(好吧,如果最后一列比前两列短,而不是前两列)。

Another thing is that Column A and B of data (ending at row 11) might sometimes not have a value. How should I go about something like this?

另一件事是数据的A列和B列(在第11行结束)有时可能没有值。我应该怎么做这样的事情?

#3

If you have a column that has data in each row of the table and the there is an empty cell between that on wanted data

如果您的列在表的每一行中都有数据,并且在所需数据之间有一个空单元格

xlws.Range("B1").End(Excel.XlDirection.xlDown).Row

Alternately, you can take the bottom up approach if the only there is no unwanted data at the end of a column.

或者,如果列末尾没有不需要的数据,您可以采用自下而上的方法。

xlws.Range("B" & xlws.Rows.Count).End(Excel.XlDirection.xlUp).Row

xlws.Range(“B”&xlws.Rows.Count).End(Excel.XlDirection.xlUp).Row

#1