Application Defined Error

[Friday]

Can anyone tell me why this code throws a Run Time Error 1004?

1. Dim r As Range, s As Range
2. Dim y As Integer
3.
4. Set r = ActiveSheet.Range("A65536").End(xlUp).Offset(-y + 1)
5. Set s = ActiveSheet.Range("A65536").End(xlUp)
6. Range(r, s).EntireRow.Delete

Note: the value of y is set prior to this and is working. The error is produced at line 4.

 

[unmarkedhelicopter]

I'd guess that you need co-ordinates (a pair of) for the offset
try :- .offset(1-y,0)

or just include the comma, note if y = 1 or greater this will error anyway

 

[Friday]

unmarkedhelicopter:

y = 54 in the sheet I am working on right now. It is the row below where the data I want to keep is (delete all rows below row 53). Are you saying with this code, it will always error if y is greater than 1?

 

[unmarkedhelicopter]

Well yes ... by definition you can't have a row less than 1.
So when you have -y + 1 this is the same as 1 - y so even 1 will give you an error as 1 - 1 = 0, and there is no such thing as row 0.

If your post #3 was what you want then I'd go with :-

Sub Delete54On
    Dim lnA As long
    lnA = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    If lnA > 53 Then Rows(54 & ":" & lnA).Delete Shift:=xlUp
End If

Don't use a number in your row test as some versions of Excel only had 32k rows and 2007 has a damn site more than 64k. The test is there to ensure the sub won't do anything without detected rows (in col A) being greater than 53.

 

[Friday]

Well yes ... by definition you can't have a row less than 1.
So when you have -y + 1 this is the same as 1 - y so even 1 will give you an error as 1 - 1 = 0, and there is no such thing as row 0.

If your post #3 was what you want then I'd go with :-

Sub Delete54On
    Dim lnA As long
    lnA = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    If lnA > 53 Then Rows(54 & ":" & lnA).Delete Shift:=xlUp
End If

Don't use a number in your row test as some versions of Excel only had 32k rows and 2007 has a damn site more than 64k. The test is there to ensure the sub won't do anything without detected rows (in col A) being greater than 53.

Got it. It's working right now. Thanks much.

 

[boblarson]

...2007 has a damn site more than 64k.

Just an FYI (quoting from the Excel 2007 help file):

1,048,576 rows by 16,384 columns