Run-time error - I can't find the issue

[giddyup]

Hi,
I have been receiving a Run-time error 1004 "Application-defined or object-defined error". I have been reading threads for a about a week with no luck. Please help!

This is the code and the line with the error is bolded below. The purpose is to have it loop through the column and find specfic words in coulmn "F" and then delete the entire row if found. The amount of rows changes so i can't set it to end at a specific row#.


Sub test()
'
' test Macro
' test
'
' Keyboard Shortcut: Ctrl+1
'
Dim workrange As Range
Dim Firstrow As Integer
Dim Lastrow As Integer
Dim lrow As Integer


Range("F:F").Select
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.Range("F1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row


For lrow = Lastrow To Firstrow Step -1
Set workrange = Cells(lrow, 6)
If workrange.Value = "ENRL" _
Or workrange.Value = "INCO" _
Or workrange.Value = "DROP" _
Or workrange.Value = "PLAN" _
Or workrange.Value = "WTLT" _
Or workrange.Value = "DECL" _
Or workrange.Value = "CANC" _
Or workrange.Value = "INPO" _
Then workrange.EntireRow.delete
Next lrow
End Sub

Any help is greatly appricated!!

 

[Lee.]

What's the purpose of the offset? Are you not counting the number of rows in column F?

Sub test()
'
' test Macro
' test
'
' Keyboard Shortcut: Ctrl+1
'
    Dim workrange As Range
    Dim Firstrow, Lastrow, lrow As Integer


    Firstrow = ActiveSheet.UsedRange.Cells(1).Row
    Lastrow = ActiveSheet.Range("F60000").End(xlUp).Row




    For lrow = Lastrow To Firstrow Step -1
        Set workrange = Cells(lrow, 6)
        If workrange.Value = "ENRL" _
           Or workrange.Value = "INCO" _
           Or workrange.Value = "DROP" _
           Or workrange.Value = "PLAN" _
           Or workrange.Value = "WTLT" _
           Or workrange.Value = "DECL" _
           Or workrange.Value = "CANC" _
           Or workrange.Value = "INPO" _
           Then workrange.EntireRow.Delete
    Next lrow
End Sub

 

[giddyup]

I'm really new to VBA, so I thought the offset was the only way to loop through the column.

Thank you your new code worked perfectly!

 

[Momentman]

Best would be to use

 Lastrow = ActiveSheet.Range("F" & rows.count).End(xlUp).Row

, this would work for rows even above 60000

 

[Snakehips]

giddyup,

The original post error is likely because you do not have a sheet named Sheet1 ???
If Sheet1 replaced with a valid sheet name then it would work or if no sheet name used at all it would work.
However, the offset is not necessary and in truth confusing.

'Error likely because you have no Sheet1
Lastrow = ActiveSheet.Range("F1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row

'Would be ok as ....

Lastrow = Range("F1").Offset(Rows.Count - 1, 0).End(xlUp).Row

'or if  Sheet1 replaced with a valid sheet name
'but offset not necessary

Lee has used a simpler construct but is using a 60000 row limit for 'looking upwards' from.
It may be unusual to have more than 60000 rows of data but it happens.

If you use Rows.Count then it will 'look upwards' from the last row, irrespective of which Excel version you are using.

Lastrow = Cells(Rows.Count, "F").End(xlUp).Row