Ask Your Question

hide columns via code

asked 2019-03-07 18:50:19 +0200

Dennis1234 gravatar image

updated 2019-03-10 08:06:48 +0200

I would like to hide columns on click with a push button. I have set this up with a macro, but get inconsistent results. My current macro, keeps hiding the same number of columns on multiple clicks.

I would like to code it like VBA to hide a range of columns. Like D to F or AF to AH.

I know how to set this up in Excel, not in libre calc.

This is what I have so far....

sub Main
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
end sub



Update: 3/10/2019 This is what worked for me.....

Sub Main
dim document   as object
dim theRange as object
document   = ThisComponent.CurrentController.Frame
theRange = ThisComponent.Sheets.GetByIndex(0).getCellRangeByName("G1:J1")
theRange.Columns.IsVisible = False
End Sub

I just change the G1:J1 for other groups of columns, I change the False to True to show them again.

Thanks for all the help!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-03-07 19:28:55 +0200

Lupp gravatar image

updated 2019-03-07 19:30:24 +0200


theRange = ... REM any rectangular range of any spreadsheet.
theRange.Columns.IsVisible = False
edit flag offensive delete link more


Worksheets(“Sheet1”).Range(“A:E”).EntireColumn.Hidden = True this is the syntax for excel, what is the syntax for libreoffice?

theRange = ("A:E")
theRange.Columns.IsVisible = False

Do I need all the other stuff, the Dim statements, dispatcher?

Thanks, ?

Dennis1234 gravatar imageDennis1234 ( 2019-03-09 08:00:44 +0200 )edit

Here are some samples to define range object:

theRange = ThisComponent.Sheets(0).getCellRangeByName("A:E") 'Get sheet by it's index (0 based), range by name
theRange = ThisComponent.Sheets.GetByIndex(0).getCellRangeByName("A1:E1")  'Get sheet by it's index (0 based), range by name, same as above, a bit different syntax
theRange = ThisComponent.Sheets.GetByName("Sheet1").getCellRangeByPosition(0,0,4,0) 'Get sheet by it's name, cell range by it's position/coordinates
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("A:E") 'Get currently active sheet, range by name
theRange = ThisComponent.Sheets.getCellRangesByName("Sheet2.A:E")(0) 'Get range directly by it's name. Method returns an array of objects, so you also need to define element index
SM_Riga gravatar imageSM_Riga ( 2019-03-09 20:48:43 +0200 )edit

Quoting @Dennis1234: "Do I need all the other stuff, the Dim statements, dispatcher?" No. You only need a CellRange-object. Apply the second line of the code I posted to it. Done!
To obtain a CellRange you basically have three ways:
-1- Use mySheet.GetCellRangeByPopsition() -2- Use mySheet.getCellRangeByName(theRangeAddress_ASSTRING)
-3- Access by index (0-based) one of the ranges belonging to a CellRanges object (as you get it as a CurrentSelection e.g.). Since a single-cell-object also supports the needed service, you can use it in place of a CellRange object, too.

Lupp gravatar imageLupp ( 2019-03-10 19:40:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-07 18:50:19 +0200

Seen: 198 times

Last updated: Mar 10 '19