UsedRange.AutoFilter error 424 object Required

bari2jitu

New Member
Joined
Apr 18, 2019
Messages
3
Below code give error msg 424 object required when it come to Usedrange.AutoFilter code line.

Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
UsedRange is a property of a worksheet, you can't use it on its own.

Try replacing UsedRange with ws.UsedRange.
 

bari2jitu

New Member
Joined
Apr 18, 2019
Messages
3
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

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

New Member
Joined
Apr 18, 2019
Messages
3
I try with below code

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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,890
Messages
5,638,860
Members
417,055
Latest member
SerenaCruz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top