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

[Darren Smith]

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"

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

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

 

[Fluff]

You've already got a filter applied to col BA, so you need to remove it.

 

[RoryA]

You've already got an autofilter applied to just one of the columns, so you need to remove that first. Add:

    wksData.AutoFilterMode = False

before the autofilter line.

 

[Darren Smith]

wksData.AutoFilterMode = False

Your code works works
But now it clears the whole column ??
The dates are in the column I did check the column

 

[RoryA]

You removed the code I suggested before…

 

[Darren Smith]

You removed the code I suggested before…

I have not removed any of your code

 

[Darren Smith]

Also, I need column 10 needs to be filtered so there is only blanks showing

 

[RoryA]

I have not removed any of your code

Then where is the Clng(CDate(…)) in that file?

 

[Darren Smith]

Then where is the Clng(CDate(…)) in that file?

Please can you add this to the code as I am not sure what you mean?
Then send it to me?
Sorry, about this...

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 auto filter.

 

[RoryA]

As in post 4:

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

 

[Darren Smith]

Thanks all fine