Custom Autofilter yields no results

R

rjejyork

I have a worksheet, where I am trying to do a custom autofilter using the
"ends with" criteria. I've had success in another worksheet with this, but
everytime I do it in the new one it does not yield any results. i've made
sure the cell formatting is correct. Any thoughts or suggestions why this
isnt working
 
D

Dennis

Check your AutoFilter Range.

Unapply the AutoFilter
"Paint the Range" then Re-apply the AutoFilter

If there are Blank cells on the Criteria range AutoFilter may guess
wrong as to your desired range

HTH

Dennis
 
G

Gord Dibben

Cell formatting should have no effect on the "ends with" criterion.

Formatting does not change the actual content of a cell, just the appearance of
the content.

Are you sure you have the entire range selected?

Do you have a typo in the "ends with" dialog?


Gord Dibben MS Excel MVP
 
R

rjejyork

Why would it matter if the value of the cell in which I am trying to use the
"ends with" criteria, are all numbers. None of the suggestions worked that
you both stated. As a test, I took a small sampling of my spreadsheet, added
a letter value to the beginning of each number value and now the "ends with"
works.

How can I get this to work without having to add a letter to each cell value
just because it is all numbers? Help?
 
G

Gord Dibben

Excel does not recognize numerics as "ending with".

That is reserved for text cells.

If you pre-format the cells as text before entering the numbers, the numbers
will act like text.

Since you have your numbers already in place, you have to append a letter or
apostrophe to the beginning of each cell.

Is there any other criterion you could use rather than "ends with"?

Maybe greater than or somesuch?

If not, here is a macro to add an apostrophe or any text in every cell in your
selected range.

Note: after running, you will not be able to calculate those cells.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
End Sub


Gord

Why would it matter if the value of the cell in which I am trying to use the
"ends with" criteria, are all numbers. None of the suggestions worked that
you both stated. As a test, I took a small sampling of my spreadsheet, added
a letter value to the beginning of each number value and now the "ends with"
works.

How can I get this to work without having to add a letter to each cell value
just because it is all numbers? Help?
Click to expand...

Gord Dibben MS Excel MVP
 
Top