Ask Your Question

hide row based on passed date

asked 2017-05-11 17:18:25 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

My first column is date, and then there are 12 more columns for different information. Every row starts with a different date, followed by this other information. When the date is passed, I want the hole row (with passed date) to "disappear". - Manually it is easy, just select row range, right click, select hide row.

How do I make this automatically, when starting the file another day?

Version: Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc CPU-treads: 4; Version OS: Windows 6.2; UI-rendering: standard; Language: sv-SE (sv_SE); Calc: group

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-05-11 21:26:23 +0200

JohnSUN gravatar image

Just add anywhere in your sheet two cells. In the first of them input name of column of dates, in the second input formula =TODAY(). Set name for this range and set it as filter criteria. Apply Advanced Filter to your data range. Write small macro like as this:

Sub RefreshFilter
    dispatcher = createUnoService("").executeDispatch( _
        ThisComponent.CurrentController.Frame, ".uno:DataAreaRefresh", "", 0, Array())
End Sub

Call it on event Document Open

HowTo Create Filter

(Full version of animation see here)

edit flag offensive delete link more

answered 2017-05-11 23:24:12 +0200

librebel gravatar image

updated 2017-05-11 23:27:46 +0200

You could use the macro HideRowValue() for this

Sub HideRowValue( strSheetName As String, iColumnIndex As Integer, oValueToHide, Optional bFindAll As Boolean )
REM This hides a Row in Calc if it contains a specific value inside a particular Column.
REM <strSheetName>: The name of the Sheet containing the Column with index=iColumnIndex.
REM <iColumnIndex>: 0-based index of the Column that holds the values to check.
REM                 ( the values in this Column should be of the same Type as the value to hide ).
REM <oValueToHide>: If this value is found in the cell, then the entire Row will be made hidden.
REM <bFindAll>:     True = Hide ALL occurences; False = Hide only the first occurence.
    Dim oSheet : oSheet = ThisComponent.Sheets.getByName( strSheetName )

    REM Traverse Rows:  99999= set here your maximum number of Rows to check...
    For i = 0 to 99999
        oCell = oSheet.getCellByPosition( iColumnIndex, i )
        If oCell.Value = oValueToHide Then
            oSheet.Rows.getByIndex( i ).isVisible = False
            If Not bFindAll Then Exit Sub
        End If
    Next i

End Sub

in your case you could call something like HideRowValue( "Sheet1", 0, CDate("17-04-2015") ) Then you could connect it to the Sheet Event "Activate Document".

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-11 17:18:25 +0200

Seen: 54 times

Last updated: May 11 '17