blocks|key|321574|text|我想补充一下Siddarth+Rout给出的答案，即可以跳过CountA调用，方法是让Find返回一个Range对象，而不是行号，然后测试返回的Range对象是否为Nothing+(空白工作表)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|321575|此外，对于空白工作表，我会让我的LastRow过程版本返回0，这样我就可以知道它是空的。|321576|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

<p>I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet). </p>

<p>Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.</p>


blocks|key|538514|text|使用该解决方案时需要记住的一个重要注意事项是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|538515|LastRow+=+ws.Cells.Find(What:="*",+After:=ws.range("a1"),+SearchOrder:=xlByRows,+SearchDirection:=xlPrevious).Row|code-block|syntax|javascript|538516|..。是为了确保您的LastRow变量是Long类型：|offset|length|style|CODE|538517|Dim+LastRow+as+Long|538518|否则，在.XLSX工作簿中的某些情况下，最终会出现溢出错误|538519|这是我的封装函数，我将其放入各种代码中使用。|538520|Private+Function+FindLastRow(ws+As+Worksheet)+As+Long
++++'+--------------------------------------------------------------------------------
++++'+Find+the+last+used+Row+on+a+Worksheet
++++'+--------------------------------------------------------------------------------
++++If+WorksheetFunction.CountA(ws.Cells)+>+0+Then
++++++++'+Search+for+any+entry,+by+searching+backwards+by+Rows.
++++++++FindLastRow+=+ws.Cells.Find(What:="*",+After:=ws.range("a1"),+SearchOrder:=xlByRows,+SearchDirection:=xlPrevious).Row
++++End+If
End+Function|538521|entityMap^0|0|0|A|7|K|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

<p>One important note to keep in mind when using the solution ...</p>

<pre><code>LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
</code></pre>

<p>... is to ensure that your <code>LastRow</code> variable is of <code>Long</code> type:</p>

<pre><code>Dim LastRow as Long
</code></pre>

<p>Otherwise you will end up getting OVERFLOW errors in certain situations in .XLSX workbooks</p>

<p>This is my encapsulated function that I drop in to various code uses.</p>

<pre><code>Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) &gt; 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function
</code></pre>


blocks|key|538525|text|Sub+lastRow()

++++Dim+i+As+Long
++++++++i+=+Cells(Rows.Count,+1).End(xlUp).Row
++++++++++++MsgBox+i

End+Sub

sub+LastRow()

'Paste+&+for+better+understanding+of+the+working+use+F8+Key+to+run+the+code+.

dim+WS+as+worksheet
dim+i+as+long

set+ws+=+thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i=+activecell.row

msgbox+"My+Last+Row+Is+"+&+i

End+sub|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|538526|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste &amp; for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " &amp; i

End sub
</code></pre>


blocks|key|538618|text|在过去的3%2B年中，我使用以下函数来查找每个已定义的列(行)和行(列)的最后一行和最后一列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|538619|最后一列：|538620|Function+lastCol(Optional+wsName+As+String,+Optional+rowToCheck+As+Long+=+1)+As+Long

++++Dim+ws++As+Worksheet

++++If+wsName+=+vbNullString+Then
++++++++Set+ws+=+ActiveSheet
++++Else
++++++++Set+ws+=+Worksheets(wsName)
++++End+If

++++lastCol+=+ws.Cells(rowToCheck,+ws.Columns.Count).End(xlToLeft).Column

End+Function|code-block|syntax|javascript|538621|最后一行：|538622|Function+lastRow(Optional+wsName+As+String,+Optional+columnToCheck+As+Long+=+1)+As+Long

++++Dim+ws+As+Worksheet

++++If+wsName+=+vbNullString+Then
++++++++Set+ws+=+ActiveSheet
++++Else
++++++++Set+ws+=+Worksheets(wsName)
++++End+If

++++lastRow+=+ws.Cells(ws.Rows.Count,+columnToCheck).End(xlUp).Row

End+Function|538623|对于OP，这是获取列E中最后一行的方法|offset|length|style|CODE|538624|Debug.Print+lastRow(columnToCheck:=Range("E4:E48").Column)|538625|最后一行，计算包含数据的空行：|538626|在这里，我们可以使用well-known+Excel+formulas，它提供了Excel中工作表的最后一行，而不涉及VBA+-+=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)|538627|为了将其放在VBA中，而不是在Excel中编写任何内容，请使用后一个函数的参数，请注意以下内容：|538628|Public+Function+LastRowWithHidden(Optional+wsName+As+String,+Optional+columnToCheck+As+Long+=+1)+As+Long

++++Dim+ws+As+Worksheet

++++If+wsName+=+vbNullString+Then
++++++++Set+ws+=+ActiveSheet
++++Else
++++++++Set+ws+=+Worksheets(wsName)
++++End+If

++++Dim+letters+As+String
++++letters+=+ColLettersGenerator(columnToCheck)
++++LastRowWithHidden+=+ws.Evaluate("=IFERROR(LOOKUP(2,1/(NOT(ISBLANK("+&+letters+&+"))),ROW("+&+letters+&+"+)),0)")

End+Function

Function+ColLettersGenerator(col+As+Long)+As+String

++++Dim+result+As+Variant
++++result+=+Split(Cells(1,+col).Address(True,+False),+"$")
++++ColLettersGenerator+=+result(0)+&+":"+&+result(0)

End+Function|538629|entityMap|0|LINK|mutability|MUTABLE|url|https://www.vitoshacademy.com/last-used-row-last-used-column-in-excel-with-formula-without-vba/^0|0|0|0|0|0|A|1|0|0|1M|0|0|1T|1G|A|P|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1F|8|@$O|1G|P|1H|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1I|8|@$O|1J|P|1K|Q|R]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1M|8|@$O|1N|P|1O|Q|R]]|9|@$O|1P|P|1Q|1|1R]]|A|$]]|$1|Y|3|Z|5|6|7|1S|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1T|8|@]|9|@]|A|$G|H]]|$1|12|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

<p>For the last 3+ years these are the functions that I am using for finding last row and last column per defined column(for row) and row(for column):</p>

<h2>Last Column:</h2>

<pre><code>Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function
</code></pre>

<h2>Last Row:</h2>

<pre><code>Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function
</code></pre>

<p>For the case of the OP, this is the way to get the last row in column <code>E</code>:</p>

<p><code>Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)</code></p>

<h2>Last Row, counting empty rows with data:</h2>

<p>Here we may use the <a href="https://www.vitoshacademy.com/last-used-row-last-used-column-in-excel-with-formula-without-vba/" rel="nofollow noreferrer">well-known Excel formulas</a>, which give us the last row of a worksheet in Excel, without involving VBA - <code>=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)</code></p>

<p>In order to put this in VBA and not to write anything in Excel, using the parameters for the latter functions, something like this could be in mind:</p>

<pre><code>Public Function LastRowWithHidden(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    Dim letters As String
    letters = ColLettersGenerator(columnToCheck)
    LastRowWithHidden = ws.Evaluate("=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(" &amp; letters &amp; "))),ROW(" &amp; letters &amp; " )),0)")

End Function

Function ColLettersGenerator(col As Long) As String

    Dim result As Variant
    result = Split(Cells(1, col).Address(True, False), "$")
    ColLettersGenerator = result(0) &amp; ":" &amp; result(0)

End Function
</code></pre>


<p>When I want to find the last used cell value, I use:</p>
<pre><code>Dim LastRow As Long

LastRow = Range(&quot;E4:E48&quot;).End(xlDown).Row

Debug.Print LastRow
</code></pre>
<p>I'm getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct.
What's the reason behind this?</p>


Error in finding last used cell in Excel with VBA

当我想要查找最后使用的单元格值时，我使用：Dim LastRow As LongLastRow = Range("E4:E48").End(xlDown).RowDebug.Print LastRow当我把一个元素放入一个单元格时，我得到了错误的输出。但是当我在单元格中输入多个值时，输出是正确的。这背后的原因是什么？

问使用VBA在Excel中查找上次使用的单元格时出错
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用VBA在Excel中查找上次使用的单元格时出错EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用VBA在Excel中查找上次使用的单元格时出错
EN