Autofilter Returns No Results to Set Range

[BrittKnee]

Hi,

Part of a macro I have created filters to a value where it replaces the name of the returned records to something else. I am running into issues where the filter returns no results. I've tried a few different things, but can't get it to work. I'd rather not use On Error. Any help is appreciated. See the code below:

'Change Name > 31 Characters

    StrOldValue = "Security Business Resource Management"
    StrNewValue = "Security Bus Resource Mgmt"
    
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=StrOldValue


    If Cells(ActiveSheet.AutoFilter.Range.Rows.Count + 1, 1).End(xlUp).Row > 1 Then
        'MsgBox "No records found.", , "No records found"
        'Exit Sub
   

      With ActiveSheet.AutoFilter.Range
        Set RngRange01 = Range("Detail[Department]").SpecialCells(xlCellTypeVisible)
        
        RngRange01.Value = StrNewValue
    
  End With
End If
 
 ActiveSheet.ListObjects(1).AutoFilter.ShowAllData

 

[davesexcel]

Hard to tell, looks like you are not telling it what to filter.