Select range of rows to delete in Excel 2007. Runtime error 1004?

I'm trying to write a code that deletes all rows between the 3rd and last data row on a worksheet. I have some short lines of code that first looks for the last row containing data, returns that row number. Subtracts 1 from it. And selects the data range from 3rd row to the 2nd to last row and attempts to delete them. But I run into error every t开发者_StackOverflow中文版ime I run this code. Any suggestions?

Sheets("Sheet1").Activate
lastrow = (Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row) - 1
Range("3: lastrow").Select  'Error 1004: method range of object _global failed
Selection.Delete Shift:=xlUp 

Using the SpecialCells property of the range:

 Range("A3:" & Range("A1").SpecialCells(xlCellTypeLastCell).Address).Select 

This will select the block up until the last used cell.

Edit: To incorporate it into your program to get the second to last, do some string manipulations on the last cell.

Dim str, str1, str2, add As String
Dim index, num As Integer
str = Range("A1").SpecialCells(xlCellTypeLastCell).Address   'returns say $j$20

index = InStr(2, str, "$")  'find the second dollar sign

str1 = Left(str, index)     'gets the string "$j$"
str2 = Mid(str, index + 1)  'get the string "20" 

num = CInt(str2) 'convert "20" to 20
num = num - 1
add = str1 & CStr(num) 'reattach to form "$j$19"

Range("A3:" & add).Select