• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting bug on 'T_Sht.AutoFilterMode = False' line

ThrottleWorks

Excel Ninja
Hi,

I am running below code.
This code is run on two different locations.

On one location code runs fine.
However at another location macros face bug at 'T_Sht.AutoFilterMode = False' line.
Also this bug is not permanent, sometimes macro will face bug and sometime macro will not face any issue while running.

Not able to understand why this is happening.
Can anyone please help me in this.

Code:
Sub Test()
    Dim TempLr As Long
    Dim TempCol As Long
    Dim TempRng As Range

    Set T_Sht = ThisWorkbook.Worksheets("T Sheet")
    ThisWorkbook.Activate
    T_Sht.Select

    On Error Resume Next
        If ActiveSheet.Name <> "T Sheet" Then
            MsgBox "T Sheet not present in the macro", vbCritical
            End
        End If
    On Error GoTo 0

    TempCol = 10 'Change column reference
    TempLr = T_Sht.Cells(T_Sht.Rows.Count, 1).End(xlUp).Row
    Set TempRng = T_Sht.Range(T_Sht.Cells(1, 1), T_Sht.Cells(TempLr, 30))
    TempRng.AutoFilter Field:=TempCol, Criteria1:="=#N/A", Operator:=xlOr, Criteria2:="="

    TempLr = T_Sht.Cells(T_Sht.Rows.Count, 1).End(xlUp).Row
    If TempLr <> 1 Then
        Set TempRng = T_Sht.Range(T_Sht.Cells(2, 1), T_Sht.Cells(TempLr, 30))
        TempRng.SpecialCells(xlCellTypeVisible).Delete
    End If

    On Error Resume Next
        T_Sht.AutoFilterMode = False
        T_Sht.AutoFilterMode = False
    On Error GoTo 0
End Sub
 

Marc L

Excel Ninja
Hi !​
However at another location macros face bug at 'T_Sht.AutoFilterMode = False' line.
  • Not possible with On Error Resumer Next just before ‼

  • And why this duplicate codeline ?! Has no sense …

  • You forgot to release the object variable - often useless with sheets as CodeNames already exist - before the procedure ends.
A VBA help must see : FilterMode
 

ThrottleWorks

Excel Ninja
Hi @vletm sir, thanks for the help.
This module is part of the macro I have written.

This module is called by another module. I have tested it on both the locations using same files.
But one location does not face any issue, and another location faces this issue sometimes.

Not able to understand why this happens. Have a nice day ahead. :)
 

vletm

Excel Ninja
ThrottleWorks
I did two questions ...
#1 ... seem that You don't have an idea
#2 ... as well as You haven't test as I asked
means ... that You would like to keep this like a challenge for Yourself
 
Top