Can I create horizontal autofilter in OpenOffice Calc

Question

The autofilter is sorting data vertically, but I want to filter rows horizontally. Lets say that I have the following table:

1 2 2 1 2

B A E F F

B D E F F

C D E F F

What I can do is to set an autofilter and filter only the rows containing "B" in the first column. What I would like to do is to filter only the rows that contain "2" (in this case the rows are second, third and the last in this case).

I have found some information regarding this matter. All of the answers I found are containing some macros to get the job done, but they were written for MS Excel, and are not compatible with OpenOffice

For example, this macros should get the rows filtered, but is not working in OpenOffice Calc:

Option Explicit

Sub horizontal_filter()
'Erik Van Geit
'060910

Dim LC As Integer           'Last Column
Dim R As Long
Dim i As Integer
Dim FilterValue As String

Const FilterColumn = 1      '1 is most logical value but you may change this

R = ActiveCell.Row
LC = Cells(R, Columns.Count).End(xlToLeft).Column

FilterValue = Cells(R, FilterColumn)

Application.ScreenUpdating = False

'to filter starting after FilterColumn
For i = FilterColumn + 1 To LC
'to filter all columns even before the filtercolumn
'For i = 1 To LC
    If i <> FilterColumn Then
    Columns(i).Hidden = Cells(R, i) <> FilterValue
    End If
Next i

Application.ScreenUpdating = True

End Sub

Any help is greatly appreciated!

do you want to display the 1st row? or the 2nd, 3rd and 5th column? or the 2nd, 3rd and 5th row based on information from the columns? — Aprillion, May 18 '12 at 17:39

tohuwawohu · Accepted Answer · 2012-05-19T10:26:47.560

You can't, under the assumption of reasonable expense. It's much easier just to transform your data so that rows get columns and vice versa. So, i would strongly recommend transforming the data using Paste Special together with the Transpose option. You could even do this dynamically by using the TRANSPOSE() function.

EDIT:

Now i got it - you want to hide columns based on a certain value. This is possible using a macro in fact, so my first answer was incorrect - sorry for that! There are some macros around that will do this for you. You can combine such a solution with an auto filter. Here's a solution by king_026 from the OpenOffice.org forums (slightly adapted to table structure - see below):

REM  *****  BASIC  *****
sub hide
   rem ----------------------------------------------------------------------
   rem define variables
   dim document   as object
   dim dispatcher as object
   rem ----------------------------------------------------------------------
   rem get access to the document
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

   rem get the current column
   nCol = ThisComponent.CurrentSelection.CellAddress.Column

   rem set the properties for moving right
   dim args2(1) as new com.sun.star.beans.PropertyValue
   args2(0).Name = "By"
   args2(0).Value = 1
   args2(1).Name = "Sel"
   args2(1).Value = false

   rem make thecurrent column counter
   dim cCol as integer
   CCol = 0

   rem goto the first column
   dim args1(0) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "ToPoint"
   args1(0).Value = "$A$2"

   dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

   rem loop until you get back to the selected cell
    Do Until cCol > nCol

    rem hide if the cell value is 1
        if ThisComponent.CurrentSelection.string <> "" and ThisComponent.CurrentSelection.value = 1 then

            rem ----------------------------------------------------------------------
            dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())

        End if

        rem goto the right nad increment the column counter
        dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
        cCol = cCol + 1

    Loop

End sub

So, the following table:

calc1

will look like this after Autofilter on Col1 and after the macro did his work:

calc

+1 - i don't agree with "you can't" part, but i agree that transposing is the way to go — Aprillion, May 18 '12 at 17:42
Well I can't do that. The reason is that at the same moment I need to have two filters - one horizontal and one vertical. So if I transform the data so the rows get columns and vice versa, I still will not be able do filter both ways. — eroteev, May 19 '12 at 05:48
Thank you for the reply tohuwawohu. In the example table of my question I would like to filter data vertically and horizontally, so I could end up with all rows that have "B" in the first column and all columns that have "2" in the first row. The autofilter is working fine for filtering rows, but after I filter the rows I want to get only the columns that have "2" on the top row. — eroteev, May 19 '12 at 08:22
@StefanEroteev: ok, now i got it. I removed my previous commentary and added some code to my answer. — tohuwawohu, May 19 '12 at 10:18
@deathApril: you were right - "you can't" was definitely wrong :) — tohuwawohu, May 19 '12 at 10:27

Can I create horizontal autofilter in OpenOffice Calc

1 Answers1