在三 (3) 个单独的工作表中查找最后一行。VBA Excel 2007
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6900658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding last rows in three(3) separate sheets. VBA Excel 2007
提问by jerryh91
I've been trying to write some codes for copying and inserting data between sheets on excel. And part of this process involves finding the last row or 2nd to last row of each of the three sheets. But after finding the last row in the first sheet, and after activating the 2nd sheet, the program keeps giving me an Error 13: type mismatch error. How should I resolve this problem??
我一直在尝试编写一些代码，用于在 excel 上的工作表之间复制和插入数据。此过程的一部分涉及查找三张纸中每张纸的最后一行或倒数第二行。但是在第一张工作表中找到最后一行并激活第二张工作表后，程序不断给我一个错误 13：类型不匹配错误。我该如何解决这个问题？？
Sub DeleteRow()
Dim StartRow, Lastrow2, NuRow As Long
StartRow = 3
Dim LastColumn As Long
Dim LastRow As Long
Dim LastCell As Long
Sheets("Sheet1").Activate
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
NuRow = LastRow - 1
Range(StartRow & ":" & NuRow).Select
Rows(StartRow & ":" & NuRow).Delete
Sheets("Sheet2").Activate
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(1 & ":" & LastRow).Select
Selection.Copy
Sheets("Sheet1").Select
Selection.Insert Shift:=xlDown
'Delete first two and last lines
Range("A1").Select
Rows("1:2").Delete
Range("A65536").End(xlUp).EntireRow.Delete Shift:=xlUp
Sheets("Sheet2").Activate
'Error 13: Type Mismatch -->Lastrow2 = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
回答by Reafidy
Below is some code based on my interpretation of what you are trying to do. You don't need to select cells to work with them and 99.9% of the time you really shouldn't.
下面是一些基于我对您要执行的操作的解释的代码。您不需要选择单元格来处理它们，而且在 99.9% 的情况下您确实不应该选择这些单元格。
Dim StartRow As Long
StartRow = 3
With Sheets("Sheet1")
LastRow = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Rows(StartRow & ":" & LastRow - 1).Delete
End With
With Sheets("Sheet2")
LastRow = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Rows(1).Resize(LastRow).Copy
.Rows(1).Resize(LastRow).Insert Shift:=xlDown
.Rows("1:2").Delete
.Range("A" & Rows.Count).End(xlUp).EntireRow.Delete Shift:=xlUp
End With
Also when dimensioning variables you should dimension like this:
此外，在标注变量时，您应该像这样标注尺寸：
Dim StartRow as long, Lastrow2 as long, NuRow As Long
In your orginal code as you have only dimensioned the last variable, startrow and lastrow2 will be variant datatypes not long.
在您的原始代码中，因为您只标注了最后一个变量，所以 startrow 和 lastrow2 将是不长的变体数据类型。
回答by Vinny Roe
If you have contiguous data, you can use CurrentRegion, or if you know you have data all the way down in e.g. Col A, then use
如果你有连续的数据，你可以使用 CurrentRegion，或者如果你知道你在 Col A 中一直有数据，然后使用
lrow = Range("A65000").end(xlup).row
to find the last row.
找到最后一行。
