I tested the Standard Filter to remove duplicates. Numbers are treated as strings, the first occurrence remains. The formats are copied. The ‘Case sensitive’ checkbox has no effect (bug?): it is always case sensitive. But this is inconvenient!
remove-duplicates.ods (11.4 KB)
yes thanks Kamil that was quite interesting. but i need to do some python learning to test it that way. i assume it wont be so hard coz lots of coding experience in some other languages like vba
the libre application on our client system doesnt support python etc. and no plugins etc. can be downloaded installed.
caught up with some other modules in this libre app im building so havent tried this yet. will let u know ok
Many thanks Eeigor
will try that along with dear Kamil’s solution but please confirm 1 thing
the worksheets i will be performing these macros on, will be hidden from the user’s view.
so the quest is - will these sort of codes need bringing the sheet into view and selecting it etc ?
I have written several of these macros in vba involving worksheets that dont need to mimic things like first select the sheet or range etc.
i mean those macros run fine without such actions.
wondering if Libre has similiar capabilities especially in this solution u’ve given
Yes, it is necessary. This is a typical example. If you want to work with a certain range (whether on a hidden sheet), but not with the range selected by the user, add the appropriate parameter to the procedure and pass it.
Sub RemoveDuplicates(oRange As Object)
@bloke, if you are satisfied with the solution, mark it. By the way, I use the VBA Collection object. LO Basic supports it in native mode. This should be familiar to you.
yes just saw Collection earlier today . same as VBA. will try ur code and some r&d
yes will mark it and thanks for your interest
Please note that both StandardFilter and AdvancedFilter cannot delete duplicates in case-insensitive mode! No tricks helped.
remove-duplicates.ods (11.7 KB)
@eeigor:
If you found a bug to report then please report a bug and link back to it here when created. Thanks.
For those who work with Cyrillic, I should inform you that, unlike VBA, the ‘Key’ string parameter of the ‘Add’ method of the ‘Collection’ object, although case insensitive, is only for ANSII characters. So replace CStr(item) with UCase(item).
Who can explain - why?
VBA Test
Sub Test_Collection()
Dim c As Collection
Dim item
On Error Resume Next
Set c = New Collection
c.Add 1, "D"
c.Add 2, "d"
c.Add 3, "Д" 'Cyrillic
c.Add 4, "д" 'Cyrillic; for LibreOffice set: UCase("д")
On Error GoTo 0
Debug.Print "Count: " & c.Count
For Each item In c
Debug.Print item
Next
End Sub
OUTPUT
Count: 2
1
3
It just needs filing as a bug and fixing - just like tdf#132389.