[RESOLVED] Deleting row within a range now gives error 1004

[scoobster]

The following code is set to delete any rows within the range 'picklist' where the value of column B is set to 'No'. This was working fine but now I just get a 'run time error 1004 - delete method of range class failed' at the .entirerow.delete. Even if I go back to an earlier version of the workbook then it runs fine but when I copy the same code to the latest version of the workbook then I get the error.

    Const FilterString As String = "No"
    
    Dim lngLastRow As Long
    Dim rngToCheck As Range
    Dim xval As Variant
    Dim lcol As Long
    Dim xlCalc As XlCalculation

    Call FreezeScreen
        
    worksheets("sheet1").Unprotect
    
    With worksheets("sheet1").Range("PickList")
       .AutoFilter Field:=2, Criteria1:=FilterString

       On Error Resume Next
       .SpecialCells(xlCellTypeVisible).EntireRow.Delete     ' gives error at this point if I remove the on error line
       
       AutoFilter.ShowAllData
    End With
   
    worksheets("sheet1").Protect
    
    Call UnFreezeScreen

Other modules called are below;

Sub FreezeScreen()

    With Application
'        xlCalc = .Calculation
'        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

End Sub

Sub UnFreezeScreen()

    With Application
'        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Any idea what is causing the error?

[seenu_1st]

use this as end code tag
[/CODE]

[scoobster]

ah, stupid me. Thanks - code tags now corrected.

[seenu_1st]

it may give the error , once u deleted the named range since it is not exist, so try to check if named range exists or not before delete.

[scoobster]

Hi

The range exists as I'm only deleting a few rows from it. I checked in RANGE MANAGER and can see it listed.

[seenu_1st]

the name exist, but range may not exist, try this function to check if range exist,

Private Sub CommandButton1_Click()
If NamedRangeExists("rng1") Then
    With Sheet1.Range("rng1")
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End If
End Sub

Function NamedRangeExists(strName As String, _
    Optional wbName As String) As Boolean
     'Declare variables
    Dim rngTest As Range, i As Long
     'Set workbook name if not set in function, as default/activebook
    If wbName = vbNullString Then wbName = ActiveWorkbook.Name
    With Workbooks(wbName)
        On Error Resume Next
         'Loop through all sheets in workbook.  In VBA, you MUST specify
         ' the worksheet name which the named range is found on.  Using
         ' Named Ranges in worksheet functions DO work across sheets
         ' without explicit reference.
        For i = 1 To .Sheets.Count Step 1
             'Try to set our variable as the named range.
            Set rngTest = .Sheets(i).Range(strName)
             'If there is no error then the name exists.
            If Err = 0 Then
                 'Set the function to TRUE & exit
                NamedRangeExists = True
                Exit Function
            Else
                 'Clear the error
                Err.Clear
            End If
        Next i
    End With
End Function

[scoobster]

It is strange. I ran that and it showed the range was there. Then I commented out the function call and added some code around what you supplied so that my code now looks like this;

Private Sub cmdRemoveItem_Click()
    
    Const FilterString As String = "No"
    
    Dim lngLastRow As Long
    Dim rngToCheck As Range
    Dim xval As Variant
    Dim lcol As Long
    Dim xlCalc As XlCalculation

    Call FreezeScreen
        
    worksheets("sheet1").Unprotect
    
'If NamedRangeExists("PickList") Then
    With Sheet1.Range("PickList")
       
       .AutoFilter Field:=2, Criteria1:=FilterString

       On Error Resume Next
       .SpecialCells(xlCellTypeVisible).EntireRow.Delete
       On Error GoTo 0
        
 
    End With
'End If
    
    worksheets("sheet1").Protect
     
    Call UnFreezeScreen
 
End Sub

and now it works!

One final question if I may : How do I get the filter to come back with all available records shown after the delete?

[seenu_1st]

i cudnt get u without seeing the datas, can u attach the file as zip? i hav only excel 2003.

[vbfbryce]

I believe you can use this:

Range("PickList").AutoFilter

without the arguments to show all records.

[seenu_1st]

this cud be better just to avoid turn off Autofilter if it is on.

If ActiveSheet.AutoFilterMode = False Then
    Range("PickList").AutoFilter
End If

[scoobster]

sorry to come back on this again but I'm back to getting the 1004 error with the entrirerow.delete statement. Code is unchanged from when it was running with the exception I added the .autofiltermode proposed in the last post.

I ran the 'namerangeexist' function again and it shows the range is there.

Is there any obvious things that can be wrong with the few lines of code or how I'm writing the commands?

[scoobster]

now it works again but I had to fully qualify the worksheet and range on the entirerow.delete command;

        With Sheet1.Range("PickList")
       
           .AutoFilter Field:=2, Criteria1:=FilterString

           On Error Resume Next
           worksheets("sheet1").Range("PickList").SpecialCells(xlCellTypeVisible).EntireRow.Delete
           On Error GoTo 0
        
If ActiveSheet.AutoFilterMode = False Then
    Range("PickList").AutoFilter
End If

       
        End With

Is there something with the preceding .autofilter that makes the following lines forget they are still within the .with sheet1.range("picklist") section?

[seenu_1st]

this means Activesheet range

Range("PickList").AutoFilter

if u put the .Range then it concider With sheet1

.Range("PickList").AutoFilter

[vbfbryce]

Not to sidetrack you from this question, but...

I would consider sorting the data in the range based on the Yes/No field, then finding the start and the end of the "No's" and deleting those rows.

I run into similar problems with filtering, and can't always figure out the rhyme or reason for it!

[scoobster]

All working consistently now. As I have the range as a table range rather than a normal range, I had to use ListObject to get it working.

Thanks