Finding last rows in three(3) separate sheets. VBA Excel 2007

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. Ho开发者_运维百科w should I resolve this problem??

  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  

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.

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.

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

lrow = Range("A65000").end(xlup).row

to find the last row.