LO calc macro issue - (sorting macro)

[aboudsawan]

Hello;

I’m new to LO macros “my first post”

I would like to sort a range of cells based on a column, the column cells populated via a formula.
therefore the cells are empty but not blank.

refereeing to the sample file;

1. if I do Autofilter, ascending sort based on Col 0 "with (empty) Checked ", I get get blank cells in the beginning ← not the result I want
2. if I do Autofilter, ascending sort based on Col 0 "with (empty) Unchecked ", proper sorting and empty cells ignored ← the result I want.

I would like to create a macro that mimic option 2, I was able to mimic the behavior of option 1, which not the result I want.

Basically, I have a large sheet (~4000 rows) and I need to create a macro that do sort based on a column that has strings content and populated via a a formula and not have many empty cells in the beginning.

Thanks in advance for any help

test_macro_sort.ods (16.2 KB)

[Lupp]

sub sort_test
oCellrange =thiscomponent.sheets().getByName("test").getCellrangeByName("A1:E112")
Dim aSortField(0) as New com.sun.star.table.TableSortField
REM Better: aSortFields()
REM Even if there is only one Field , you have a sequence prepared for many.
REM The singular applies to each element.

aSortField(0).Field = 2 REM Field index changed!
REM In the context this addresses colum C (0-based index 2 inside the range).

aSortField(0).IsAscending = "True"
SortDesc = oCellrange.createSortDescriptor
'A SortDescriptor is an array of 9 propertyvalues:
REM This is very irritating. It should be a structure.
REM But things are as they are due to old traditions.
REM I don't know a specification assuring the mapping
REM of indices to property names.
'0 = IsSortColumns, 
'1 = ContainsHeader,
'2 = MaxFieldCount (set to 3), 
'3 = SortFields, 
'4 = BindFormatToContent, 
'5 = CopyOutputData,
'6 = OutputPosition, 
'7 = IsUserListEnabled, 
'8 = UserListIndex
SortDesc(1).Value = TRUE 'ContainsHeader
SortDesc(3).Value = aSortField() 'SortField REM Plural again.
oCellrange.sort(SortDesc)
end sub
REM That's nothing to do with the filtering.
REM For me the above works as expected. What would you expect?
REM To sort with "field 0", That's the column containing the conditional formulas, 
REM doesn't make sense, imo. Nevertheless, the result is to be expected.
REM Why did you expect otherwise?
REM Only blöank cells are treated exceptionally.
REM Cells with empty strings as values are "LESS THAN" 
REM cells having any non-empty text (but "GREATER THAN" any number).

[aboudsawan]

Thank for the feed back, could you please provide an answer for my question.
I want to create a macro that sort a range of cells where empty cells are ignored ( please refer to my original post)

Thanks

[Lupp]

Empty (blank) cells aren’t ignored, but sorted to the end of the queue by special treatment.
But the relevant fact here is thatz there are no empty cells in the range used for the demo in column A. The formula results are empty strings, and these are treated as I told. I Thnk you can’t change that based on the sorting provided by calc itself. Of course you can write your own sorting algorithm. It will surely be very inefficient comparably.

[aboudsawan]

Hello;

more details on the issue

If I do sorting ascending on col 0, using auto filter button:
I get
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
[EMPTY CELL]
a
b

if I do sorting ascending on col 0, using auto filter button with (empty) unchecked:
I get
a
b

ie empty cell will not be in the begging.

I want to implement the second scenario using a macro

Thanks