MrExcel Publishing
Your One Stop for Excel Tips & Solutions

filtering data

Posted by Maarten on January 13, 2000 2:07 PM

I have a sheet with a large quantaty of data and in a colum I only want to show values that end with a 1 or a 6.
If I use autofilter it doesn't show any thing.
used the option of "ends with" but still nothing.
Any idears??

Posted by Celia on January 13, 2000 5:35 PM

I also can often not get the autofilter or advanced filter to do what they are supposed to do and normally have to write macros instead.
Unless someone can explain how to do what you want by using Excel's filter tool, you could use the following macro.

Sub HideRows()
Dim cell As Range
For Each cell In Selection
If Right(cell.Value, 1) <> 1 And Right _ (cell.Value, 1) <> 6 Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Posted by Maarten on January 14, 2000 12:00 AM

EXCELENT, you just saved me hours of work.
Thanks a lot for that.