Filter unique in range, only keep visible
Jim,
A bit neater again, but maybe it has to be done the messy way as I noticed
objects in the sheet are lost.
Although it is a lot more code, maybe there are advantages to do this
without the filter altogether.
So get the range in an array, filter the unique rows in the array and put it
back.
The one advantage I can see is that you do it case sensitive and case
in-sensitive.
The drawback would be it that if the range is large it might get a bit slow.
RBS
"Jim Cone" wrote in message
...
RBS,
I did play around with using only the original sheet.
However, it involved...
Using SpecialCells to get the visible range.
Looping thru each range area and writing each cell value to an array.
Placing the array on the sheet.
It wasn't very neat.
The following is an amended version of my earlier post that
is a little more compact...
Regards,
Jim Cone
San Francisco, USA
'---------------------------
Sub test()
FilterUniqueInRange ActiveSheet, ActiveWindow.RangeSelection
End Sub
'---
Sub FilterUniqueInRange(sh As Worksheet, rng As Range)
Dim strName As String
Dim shtTemp
strName = sh.Name
Set shtTemp = _
ActiveWorkbook.Sheets.Add(after:=Worksheets(strNam e), Count:=1)
sh.Activate
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=shtTemp.Range(rng(1).Address), Unique:=True
Application.DisplayAlerts = False
sh.Delete
shtTemp.Name = strName
Application.DisplayAlerts = True
Set shtTemp = Nothing
End Sub
'---------------------------
"RB Smissaert"
wrote in message
Jim,
Yes, that is a bit neater indeed.
I was hoping though that there might be a way to do away
with the temp worksheet, although it is not really a problem.
RBS
|