Why is the Autofilter saying "Autofilter method of Range Class Failed"

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
427
Office Version
  1. 2019
Platform
  1. Windows
Hi All,
This code will not work can`t work out why?
Sorry to say but this is very urgent...

This part fails it says Error 1004 "Autofilter method of Range Class Failed"

VBA Code:
With rngFull
                   .AutoFilter Field:=lngDateCol, _
                    Criteria1:=">=" & StartDate, _
                    Criteria2:="<=" & EndDate

VBA Code:
Public Sub PromptUserForInputDates()
    
    Dim strStart As String, strEnd As String, strPromptMessage As String

    strStart = InputBox("Please enter the Current JobNos. start date")
    

    If Not IsDate(strStart) Then
        strPromptMessage = "Oops! It looks like your entry is not a valid " & _
                           "date. Please retry with a valid date..."
        MsgBox strPromptMessage
        Exit Sub
    End If
    

    strEnd = InputBox("Please enter the  Current JobNos. end date")
    

    If Not IsDate(strStart) Then
        strPromptMessage = "Oops! It looks like your entry is not a valid " & _
                           "date. Please retry with a valid date..."
        MsgBox strPromptMessage
        Exit Sub
    End If

    Call CreateSubsetWorksheet(strStart, strEnd)
    
End Sub

Public Sub CreateSubsetWorksheet(StartDate As String, EndDate As String)

    Dim wksData As Worksheet, wksTarget As Worksheet
    Dim lngLastRow As Long, lngLastCol As Long, lngDateCol As Long
    Dim rngFull As Range, rngResult As Range, rngTarget As Range
    

    Set wksData = ThisWorkbook.Worksheets("TGS JOB RECORD")
    lngDateCol = 53
    
    lngLastRow = LastOccupiedRowNum(wksData)
    lngLastCol = LastOccupiedColNum(wksData)
    With wksData
        Set rngFull = wksData.Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol))
    End With

    With rngFull
                   .AutoFilter Field:=lngDateCol, _
                    Criteria1:=">=" & StartDate, _
                    Criteria2:="<=" & EndDate
                    
                    

        If wksData.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
        
            MsgBox "Oops! Those dates filter out all data!"
            
   
            wksData.AutoFilterMode = False
            If wksData.FilterMode = True Then
                wksData.ShowAllData
            End If
            Exit Sub
            
        Else
        

            Set rngResult = .SpecialCells(xlCellTypeVisible)
            
              Set wksTarget = ThisWorkbook.Worksheets.Add("Current Jobs")
            Set rngTarget = wksTarget.Cells(1, 1)
            rngResult.Copy Destination:=rngTarget

        End If
    End With
    
    
    wksData.AutoFilterMode = False
    If wksData.FilterMode = True Then
        wksData.ShowAllData
    End If
    
MsgBox "Data transferred!"

End Sub

Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
    Dim lng As Long

    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
        End With
    Else
        lng = 1
    End If
    LastOccupiedRowNum = lng
End Function

Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column
        End With
    Else
        lng = 1
    End If
    LastOccupiedColNum = lng
End Function
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,747
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You're missing the Operator argument - Operator:=xlAnd

Side note: with dates, it would be better to pass them as dates to the CreateSubsetWorksheet routine, then use CLng on them before using them in the autofilter.
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
427
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Operator:=xlAnd
Where should this go?

I created this for the number entry is this the right way?

VBA Code:
Public Sub PromptUserForInputDates()
    
    Dim strStart As String, strEnd As String, strPromptMessage As String

    strStart = InputBox("Please enter the Current JobNos. start date")
    

    If Not IsDate(strStart) Then
        strPromptMessage = "Oops! It looks like your entry is not a valid " & _
                           "date. Please retry with a valid date..."
        MsgBox strPromptMessage
        Exit Sub
    End If
    

    strEnd = InputBox("Please enter the  Current JobNos. end date")
    

    If Not IsDate(strStart) Then
        strPromptMessage = "Oops! It looks like your entry is not a valid " & _
                           "date. Please retry with a valid date..."
        MsgBox strPromptMessage
        Exit Sub
    End If

    Call CreateSubsetWorksheet(strStart, strEnd)
    
End Sub
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,747
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Simplest is just to change this line:

Code:
With rngFull
                   .AutoFilter Field:=lngDateCol, _
                    Criteria1:=">=" & StartDate, _
                    Criteria2:="<=" & EndDate

to:

Code:
With rngFull
                   .AutoFilter Field:=lngDateCol, _
                    Criteria1:=">=" & CLng(CDate(StartDate)), _
                    Criteria2:="<=" & CLng(CDate(EndDate)), Operator:=xlAnd
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
427
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

The criteria are correct with your alteration, but it is still saying the error?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,747
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the sheet protected? What is the value of lngLastCol ?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
427
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Is the sheet protected? What is the value of lngLastCol ? Value is 143
The sheet is protected
I tried to unprotect it but it says I need a password.
But I have no idea what that is or who put the protect worksheet on?
The only person to use the worksheet is me??
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,747
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If it's protected that's almost certainly why the code won't work. We can't help you with breaking passwords, I'm afraid - I assume you've tried leaving the password blank?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
427
Office Version
  1. 2019
Platform
  1. Windows
Sorry it`s not protected I got confused with a different workbook
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,747
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then I can't see why you would be getting that error, I'm afraid.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,278
Messages
5,641,279
Members
417,202
Latest member
AndyVBA

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