UsedRange.AutoFilter error 424 object Required

[bari2jitu]

Below code give error msg 424 object required when it come to Usedrange.AutoFilter code line.

Sub WorkOrderListFile()

Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer

    ' Select the used range.
    Set sheet = ActiveSheet
    sheet.UsedRange.Select


    ' Display the range's rows and columns.
    row_min = sheet.UsedRange.Row
    row_max = row_min + sheet.UsedRange.Rows.Count - 1
    col_min = sheet.UsedRange.Column
    col_max = col_min + sheet.UsedRange.Columns.Count - 1


    'This code will unmerge all the merged cells
    Worksheets("Report").Activate
    ActiveSheet.UsedRange.Select
    ActiveSheet.Cells.UnMerge
        
        'This code will give SPOC names against work order
        If Range("L1") = Empty And col_max > 9 Then
        Range("L1").Select
        Selection.Delete Shift:=xlUp


        'This code will delete all empty cells and move to left
         Cells.Select
            Selection.SpecialCells(xlCellTypeBlanks).Select
            Selection.Delete Shift:=xlToLeft


        'This will make all columns autofit
        'Cells.Columns.AutoFit
        sheet.UsedRange.Select


        Set ws = ThisWorkbook.Worksheets("Report")
         ws.Activate 'not required but allows user to view sheet if warning message appears
         
            'Clear any existing filters
            On Error Resume Next
              ws.ShowAllData
            On Error GoTo 0
                  '1. Apply Filter
                  With UsedRange
                   UsedRange.AutoFilter field:=1, Criteria1:="<>Work Order:"
                  End With
                  '2. Delete Rows
                  Application.DisplayAlerts = False
                    UsedRange.SpecialCells(xlCellTypeVisible).Delete
                  Application.DisplayAlerts = True
                  
                  '3. Clear Filter
                  On Error Resume Next
                    ws.ShowAllData
                  On Error GoTo 0






                        Cells.Columns.AutoFit
                        ActiveSheet.UsedRange.Select
                        
                        Dim rng As Range, cell As Range
                        Set rng = Selection
                        
                        For Each cell In rng
                        cell.Value = Replace(cell.Value, " ", " ")
'                        cell.Font.Color = vbRed
                        cell = Trim(cell)


                        Next cell
        End If


End Sub

 

[Norie]

UsedRange is a property of a worksheet, you can't use it on its own.

Try replacing UsedRange with ws.UsedRange.

 

[bari2jitu]

UsedRange is a property of a worksheet, you can't use it on its own.

Try replacing UsedRange with ws.UsedRange.

I try with below code

Selection.AutoFilter
    ActiveSheet.Range("$A$2:$L$1000").AutoFilter Field:=1, Criteria1:="<>Work Order:", Operator:= _
        xlFilterValues
    Selection.EntireRow.Delete
[\code]

now run time error 1004 AutoFilter method of Range class failed.

If i click on debug button and press F8 key then working fine.

 

[bari2jitu]

I try with below code

Selection.AutoFilter
    ActiveSheet.Range("$A$2:$L$1000").AutoFilter Field:=1, Criteria1:="<>Work Order:", Operator:= _
        xlFilterValues
    Selection.EntireRow.Delete
[\code]

now run time error 1004 AutoFilter method of Range class failed.

If i click on debug button and press F8 key then working fine.[/QUOTE]


[QUOTE]i used another way to delete without AutoFilter [/QUOTE]


[CODE]

Sub WorkOrderListFinal()


Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer
    ' Select the used range.
    Set sheet = ActiveSheet
    sheet.UsedRange.Select


    ' Display the range's rows and columns.
    row_min = sheet.UsedRange.Row
    row_max = row_min + sheet.UsedRange.Rows.Count - 1
    col_min = sheet.UsedRange.Column
    col_max = col_min + sheet.UsedRange.Columns.Count - 1


    'This code will unmerge all the merged cells
    Worksheets("Report").Activate
    ActiveSheet.UsedRange.Select
    ActiveSheet.Cells.UnMerge
        
        'This code will give SPOC names against work order
        If Range("L1") = Empty And col_max > 9 Then
        Range("L1").Select
        Selection.Delete Shift:=xlUp


        'This code will delete all empty cells and move to left
         sheet.UsedRange.Select
            Selection.SpecialCells(xlCellTypeBlanks).Select
            Selection.Delete Shift:=xlToLeft


        sheet.UsedRange.Select
        
'defining variables


Dim condition_range As Range
Dim condition As String
Dim i As Integer
'populating variables
                    
Set condition_range = Range("A:A")
                    
condition = "Work Order:"
                    
'loop through rows and test the condition
                    
    With condition_range
                    
        For i = Cells(.SpecialCells(xlCellTypeLastCell).Row, .Column).Row To 1 Step -1
                    
        If Cells(i, .Column) <> condition Then Rows(i).EntireRow.Delete
                    
        Next i
                    
    End With
                
    ActiveSheet.UsedRange.Select
                            
    Dim rng As Range, cell As Range
    Set rng = Selection
                            
        For Each cell In rng
        cell.Value = Replace(cell.Value, " ", " ")
        cell = Trim(cell)
    
    Next cell
End If
    Cells.Select
    Selection.ColumnWidth = 255
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit


End Sub