Filter VBA Left

[Mark F]

Hi, I'm looking to filter to show all rows where the left seven characters in cells in column Bare "Message"

Below is the code I have, but it is not working as I hope.

Thanks for any help

Mark

Sub filter()

  
    ActiveSheet.ShowAllData
     lastRow = Cells(Rows.Count, "a").End(xlUp).Row
 
         ActiveSheet.Range("A:U").AutoFilter Field:=2, Criteria1:=Left(Cells(b2).Value, 7) = "Message", Operator:= _
        xlFilterValues
     

End Sub

 

[DanteAmor]

Try:

Sub filter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  ActiveSheet.Range("A:U").AutoFilter Field:=2, Criteria1:="Message*", Operator:=xlFilterValues
End Sub

 

[Joe4]

Mark,

I also recommend that you NOT use reserved words like "filter" as the name of your procedures, functions, or variables.
Doing so can cause errors and unexpected results, as when you refer to "filter" in your code, Excel may not be able to determine if you mean the function or your procedure.
I often preface my procedures with the word "My", i.e. "MyFilter" to ensure that I don't have this issue.

 

[Mark F]

Mark,

I also recommend that you NOT use reserved words like "filter" as the name of your procedures, functions, or variables.
Doing so can cause errors and unexpected results, as when you refer to "filter" in your code, Excel may not be able to determine if you mean the function or your procedure.
I often preface my procedures with the word "My", i.e. "MyFilter" to ensure that I don't have this issue.

Thanks for the tip - I wouldn't have considered that - I had changed the name to "filter" to post here (it was a longer name) but could quite easily have called it that in the live version.

 

[tjdrake]

Mark, I'm filtering on the 21st column, but all the same. Here's what I have.

Sub cmbToggleFilter()

Sheets("Sheet1").Activate
Sheets("Sheet1").Select

Range("A1").Activate
ActiveCell.Select

If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
Else
With ActiveSheet.UsedRange
.AutoFilter Field:=21
.AutoFilter Field:=21, Criteria1:="=Message*", Operator:=xlAnd
End With
End If



ActiveSheet.Calculate
End Sub

 

[Mark F]

Try:

Sub filter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  ActiveSheet.Range("A:U").AutoFilter Field:=2, Criteria1:="Message*", Operator:=xlFilterValues
End Sub

Thanks - that works perfectly, and a simpler solution to the way I had approached it.