VBA code for Filter

[Wee Kuang]

Hello peeps,

In a column, there are data consisting of numbers 1 to 1000, "TBC" and "/". What I want Excel to do is just show rows containing the numbers only. When I record a macro and then look at the code, it is not in the way I wanted it to be. Below is the code from the recorded macro:

Sub mrbean123()
'
' mrbean123 Macro
'

'
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Rows("1:1").Select
Range("I1").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AJ$1100").AutoFilter Field:=15, Criteria1:="BCA"
ActiveSheet.Range("$A$1:$AJ$1100").AutoFilter Field:=20, Criteria1:=Array( _
"1", "100", "111", "112", "113", "114", "131", "132", "176", "177", "178", "179", "180", _
"181", "182", "183", "201", "202", "203", "204", "205", "206", "207", "208", "209", "210", _
"211", "212", "213", "214", "215", "216", "218", "219", "220", "221", "222", "225", "226", _
"245", "251", "252", "253", "254", "255", "256", "257", "301", "302", "303", "305", "306", _
"307", "308", "309", "310", "311", "312", "351", "361", "401", "402", "451", "501", "502", _
"503", "504", "51", "52", "53", "54", "55", "551", "552", "553", "554", "56", "57", "58", _
"59", "60", "601", "602", "606", "607", "608", "61", "610", "62", "63", "64", "65", "653", _
"66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "78", "79", "80", "81", "82" _
, "83", "84", "85", "86", "87", "88", "89", "91", "92", "93", "94", "95", "96", "97", "98"), _

I need to just replace the Filter code to tell Excel to exclude the rows with "TBC" and "/". Can anyone help me with this pls?

Cheers!

 

[SydneyGeek]

Try replacing everything after Rows("1:1").Select

with

    Selection.AutoFilter
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=20, Criteria1:="<>TBC", _
        Operator:=xlAnd, Criteria2:="<>/"

Denis

 

[vds1]

'use the following code
'this should help you

Sub Delete_Unwanted()
Dim intCount As Integer
Dim IntLoop As Integer ' Looping
intCount = Range("T65536").End(xlUp).Row 'to get the count of lineitem
Range("T1").Select ' select the first cell
For IntLoop = 1 To intCount
If IsNumeric(ActiveCell.Value) = False Or IsEmpty(ActiveCell.Value) = True Then
ActiveCell.Delete (xlUp)
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Next IntLoop
End Sub

 

[Wee Kuang]

Thank you SydneyGeek and vds1 for your replies. Now my code is working fine. Cheers and have a good day