Filter using vba

[zaska]

Hi all,

I have the following data and would like to filter out only "EQ" and "BE" and delete the remaining data

Thanks
<table width="722" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="10" width="64"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" width="64" height="17">SYMBOL</td> <td style="width:48pt" width="64">SERIES</td> <td style="width:48pt" width="64">OPEN</td> <td style="width:48pt" width="64">HIGH</td> <td style="width:48pt" width="64">LOW</td> <td style="width:48pt" width="64">CLOSE</td> <td style="width:48pt" width="64">LAST</td> <td style="width:48pt" width="64">PREVCLOSE</td> <td style="width:48pt" width="64">TOTTRDQTY</td> <td style="width:48pt" width="64">TOTTRDVAL</td> <td style="width:62pt" width="82">TIMESTAMP</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">20MICRONS</td> <td>EQ</td> <td align="right">49.85</td> <td align="right">49.85</td> <td align="right">46.5</td> <td align="right">47</td> <td align="right">47</td> <td align="right">47.45</td> <td align="right">11584</td> <td align="right">550157.6</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">3IINFOTECH</td> <td>N1
</td> <td align="right">42.9</td> <td align="right">44.15</td> <td align="right">42.4</td> <td align="right">43.2</td> <td align="right">43.15</td> <td align="right">42.9</td> <td align="right">1035958</td> <td align="right">45113508</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">3MINDIA</td> <td>EQ</td> <td align="right">4000</td> <td align="right">4020</td> <td align="right">3900</td> <td align="right">3970.9</td> <td align="right">3945</td> <td align="right">3916.5</td> <td align="right">302</td> <td align="right">1192493</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">A2ZMES</td> <td>BE
</td> <td align="right">238.95</td> <td align="right">261.3</td> <td align="right">237.65</td> <td align="right">243.6</td> <td align="right">244</td> <td align="right">235.9</td> <td align="right">1718837</td> <td align="right">4.34E+08</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">AANJANEYA</td> <td>EQ</td> <td align="right">380.9</td> <td align="right">385.5</td> <td align="right">371.5</td> <td align="right">376.7</td> <td align="right">372.1</td> <td align="right">376.95</td> <td align="right">1441229</td> <td align="right">5.46E+08</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">AARTIDRUGS</td> <td>EQ</td> <td align="right">125.5</td> <td align="right">128.35</td> <td align="right">125.5</td> <td align="right">127.05</td> <td align="right">127.95</td> <td align="right">126.2</td> <td align="right">816</td> <td align="right">103767.4</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">AARTIIND</td> <td>EQ</td> <td align="right">48</td> <td align="right">48.6</td> <td align="right">46.05</td> <td align="right">46.75</td> <td align="right">46.1</td> <td align="right">47.95</td> <td align="right">20136</td> <td align="right">955819.4</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">AARVEEDEN</td> <td>EQ</td> <td align="right">51.5</td> <td align="right">52.95</td> <td align="right">51.5</td> <td align="right">52.95</td> <td align="right">52.95</td> <td align="right">50.75</td> <td align="right">864</td> <td align="right">44911.05</td> <td class="xl22" align="right">21-Jun-11</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">ABAN</td> <td>EQ</td> <td align="right">523.95</td> <td align="right">532.45</td> <td align="right">514.55</td> <td align="right">518.9</td> <td align="right">517.45</td> <td align="right">522.15</td> <td align="right">543759</td> <td align="right">2.85E+08</td> <td class="xl22" align="right">21-Jun-11</td> </tr> </tbody></table>

 

[Datsmart]

This code will delete all rows that do not have "EQ or "BE" in column B.

Sub DeleteNonMatches()
Dim i As Long, LastRow As Long
'find last row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
'loop from last row to first row, delete non match rows
    For i = LastRow To 2 Step -1
        Select Case Cells(i, "B")
            Case Is = "BE", "EQ"
            Case Else
                Rows(i).Delete
        End Select
    Next i
End Sub

Be aware, if your data has spaces after the text it will be a non match and that row will be deleted.

 

[zaska]

Thank you ..your code helped me.