#1
|
|||
|
|||
Sort Filter Problem
Hi All,
I'm trying to get a sheet to sort by country where there are more than one row for each country. Now that parts not this issue, was is the problem is that when I go to sort the original, a to z Tanzania always starts the filtered list. If I then sort the list from z to a Spain now heads the list. Anyone got any ideas as to what's going on. Best Rgds, Dave R. |
#2
|
|||
|
|||
In Tanzania the T is the 3rd character in, the first 2 are unprintable characters so you can't see them.
I ran this code against one of the cells containing TANZANIA to see what was actually in the cell Code:
Sub CheckOfCharacters() Dim i As Integer, str As String str = ActiveCell.Value 'MsgBox ActiveCell.Font.Name For i = 1 To Len(str) Debug.Print Mid(str, i, 1) & " = " & Asc(Mid(str, i, 1)) Debug.Print Mid(str, i, 1) & " = " & AscW(Mid(str, i, 1)) Next End Sub Code:
Sub Clean_C_Column() Dim lastRow As Long Dim rng As Range Dim cel As Range With Sheets("Sheet1") lastRow = .Range("C" & .Rows.Count).End(xlUp).Row Set rng = .Range("C2:C" & lastRow) For Each cel In rng cel.Value = Replace(cel.Value, ChrW(8203), "") Next cel End With End Sub |
#3
|
||||
|
||||
You have 1 or 2 zero-width space characters at the left of many of your cells in the Country column. See Sheet1 (2) of the attached where I've deleted the other columns and added a formula to the right to show each character's unicode from those cells.
Those with 8203 are the zero-width spaces; 2 in front of TANZANIA and plenty of others. They shouldn't be there and have come in as a result of how you brought the data into Excel (a copy/paste from a website?). How to remove: 1. Best not to bring them in in the first place if possible - perhaps use Power Query to get that data? 2. Remove them in situ and en masse with a line of vba:
selection.Replace What:=ChrW(8203), Replacement:="", LookAt:=xlPartTest it on column C of Sheet1 (2) of the attached first. Then your sorting should be fine. |
#4
|
|||
|
|||
Hi All,
Well I'd never have thought of anything like that, I would have started again, copied the data into a new sheet and still had the problem. I did import a spreadsheet a colleague sent me and copied data from that, so something must have happened then, because his sheet lists OK. Anyway I appreciate the help and will get on to it in the morning. Best Rgds Dave R. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SORT() and FILTER() functions missing | Ricko_uk | Excel | 5 | 08-24-2020 11:44 AM |
Size of filter sort box and open at 1st available row for new data. | mobile | Excel | 0 | 10-23-2016 03:39 AM |
Filter, Sort, Macro... what to do? | zingle | Excel | 3 | 10-17-2015 06:49 AM |
Forgotten Sort and Filter Criteria | rbdmg | Mail Merge | 0 | 10-31-2011 09:14 PM |
sort & filter icon not available or icon not highlighted | survivo01 | Excel | 1 | 08-08-2011 03:41 AM |