hide columns via code

[Dennis1234]

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("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
end sub

Thanks,

D

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!

[Lupp]

Basic:

theRange = ... REM any rectangular range of any spreadsheet.
theRange.Columns.IsVisible = False

[Dennis1234]

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,
?

[SM_Riga]

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

[Lupp]

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.