这是我的剧本

Sub Update_OOR()

    Dim wsTNO As Worksheet
    Dim wsTND As Worksheet
    Dim wsTNA As Worksheet
    Dim lastrow As Long, fstcell As Long

    Set wsTNO = Sheets("Tel-Nexx OOR")
    Set wsTND = Sheets("Tel-Nexx Data")
    Set wsTNA = Sheets("Tel-Nexx Archive")

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    With Intersect(wsTNO.UsedRange, wsTNO.Columns("S"))
        .AutoFilter 1, "<>Same"
        With Intersect(.Offset(2).EntireRow, .Parent.Range("B:P"))
            .Copy wsTNA.Cells(Rows.Count, "B").End(xlUp).Offset(1)
            .EntireRow.Delete
        End With
        .AutoFilter
    End With


 Blow away rows that are useless
    lastrow = wsTND.Range("A2").End(xlDown).Row
    wsTND.Range("O1:P1").Copy wsTND.Range("O2:P" & lastrow)
    wsTND.UsedRange.Copy Sheets.Add.Range("A1")

    With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("P"))
        ActiveSheet.Range("O:P").Calculate
        .AutoFilter 1, "<>Different"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    With ActiveSheet
        lastrow = wsTND.Range("A2").End(xlDown).Row
        Intersect(.UsedRange, .Range("A2:M" & lastrow)).Copy wsTNO.Cells(Rows.Count, "B").End(xlUp).Offset(1)
        .Delete
    End With

    With wsTNO
        lastrow = wsTNO.Cells(Rows.Count, "B").End(xlUp).Row
        wsTNO.Range("T1:AD1").Copy
        wsTNO.Range("B3:N" & lastrow).PasteSpecial xlPasteFormats
        lastrow = wsTNO.Cells(Rows.Count, "R").End(xlUp).Row
        fstcell = wsTNO.Cells(Rows.Count, "N").End(xlUp).Row
        wsTNO.Range("AE1:AI1").Copy wsTNO.Range("O" & fstcell & ":S" & lastrow).Offset(1, 0)
    End With

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With

End Sub

从技术上讲,到这里为止,它的运作非常完美:

With wsTNO
        lastrow = wsTNO.Cells(Rows.Count, "B").End(xlUp).Row
        wsTNO.Range("T1:AD1").Copy
        wsTNO.Range("B3:N" & lastrow).PasteSpecial xlPasteFormats
        lastrow = wsTNO.Cells(Rows.Count, "R").End(xlUp).Row
        fstcell = wsTNO.Cells(Rows.Count, "N").End(xlUp).Row
        wsTNO.Range("AE1:AI1").Copy wsTNO.Range("O" & fstcell & ":S" & lastrow).Offset(1, 0)
End With

现在,从技术上讲,这个部分中的一切工作都是正确的, 但代码中的最后一行, 它会正确删除所有内容, 然后它就会超越了一步。 我想知道为什么。 如果我把它除掉的话, 它就会在O到S的上面的单元格里 。 我需要知道第一个和最后一个单元格, 因为数据需要只写到特定的单元格范围 。

如果有比较容易的办法这样做的话,如果有人能告诉我,如果有人不能告诉我,那么有人能告诉我如何解决这个问题吗?

谢谢

随函附上工作手册。

< a href=" "http://dl.dropbox.com/u/3327208/Excel/First%26LastRows.xlsm" rel=“no follow" >http://dl.dropbox.com/u/3327208/Excel/First%26LastRows.xlsm