0

I need some help using the autofilter in LibreOffice. Each of my filtered columns should also include empty cells. For instance, if this is the data:

Example 1

and I want to filter for GMC and truck, I would get this on each column (done in Excel using autofilter):

Example 2

but doing that in LibreOffice Calc seems impossible, even with the standard filter (which would be very cumbersome on more than just a few columns).

Can someone give me a simple way of doing this?

Community
  • 1
  • 1
jnewt
  • 197
  • 1
  • 3
  • 13

2 Answers2

0

You could fill the empty cells with a single space. This way, they still seem to be empty on screen and print, but the space character is accessible for filtering:

AutoFilter AutoFilter2

tohuwawohu
  • 13,268
  • 4
  • 42
  • 61
0

I am sure that LibreOffice will at some point fill this gap. In the meantime LibreOffice users will survive using the work-around, filling the empty cells with a single space. If you want to automate this, here's an easy Macro using LibreOffice Basic Script:

REM ***** BASIC *****

Sub ForFilter()
 oModel = thisComponent ' at first examine thisComponent
 ' xray oModel
 oSpreadSheet = oModel.getCurrentController().getActiveSheet()
 ' oSpreadSheet = oModel.getSheets().getByIndex(0)
 ' xray oSpreadSheet
 xReplaceDescr = oSpreadSheet.createReplaceDescriptor()
 ' xray xReplaceDescr
 xReplaceDescr.SearchString = ""
 xReplaceDescr.ReplaceString = " "
 lFound = oSpreadSheet.replaceAll(xReplaceDescr)
 ' xray lFound
 'MsgBox lFound & " replaced. AutoFilter should work now!"
 MsgBox "Empty cells filled. AutoFilter should work now."
End Sub


You can easily paste that under Tools > Macros > Organize Macros > LibreOffice Basic... > My Macros > Standard (> Module1), clicking 'New' .
The macro is between 'Sub' until 'End Sub'.

Create a button for the macro, e.g. on a custom toolbar:
Tools > Customize... > Toolbars > New
Name the new toolbar:
Standard2
Save in: LibreOffice Calc.

Select toolbar 'Standard2'.
Click 'Add...'.
Category:
LibreOffice Macros > My Macros > Standard > Module1
Under ‘Commands’, select ‘ForFilter’
Click ‘Add’, ‘Close’.
If you have to delete a macro, click Delete in the pull-down next to Modify.
Click ‘OK’.
Done.

I adapted a script I have found online. An apostrophe or quote sign (') at the beginning of a code line means that the line is not interpreted as code, but as a comment.
To run the macro, you can just click on the button ‘ForFilter’.

happy
  • 1