# Hide x columns to left and show x columns to right of active column

I currently have macros to hide 10 columns, then one to hide 10 columns and show then next 10 columns, then the another macro to hide 10 cells and show the previous 10 columns. Essentially, I have a budget and 10 columns represent a month, I have macro to move to the next month or previous or hide the current month. The issue is I'll end up with 36 macros for each sheet, way to much.

Here is a sample macros for a given month.

Sub Hide_November
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("DS:EB")
theRange.Columns.IsVisible = False
End Sub

Sub Show_November
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("DS:EB")
theRange.Columns.IsVisible = True
End Sub

Sub Hide_November_Show_December
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("DS:EB")
theRange.Columns.IsVisible = False
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("ED:EM")
theRange.Columns.IsVisible = True
End Sub

Sub Hide_November_Show_October
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("DS:EB")
theRange.Columns.IsVisible = False
theRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("DH:DQ")
theRange.Columns.IsVisible = True
End Sub


I trimmed out some stuff, but the general idea. This code works, but not the best way to do it. It'd be better to use the same macro to show 10, hide 10 etc...

D

edit retag close merge delete

Sort by » oldest newest most voted

-1- Sheets needing a "every-tenth-column" treatment should basically be suspected to be badly designed.
-2- You can treat the periodic groups of columns by one single couple of subroutines.

I made this example with a sensitive text-area callHideShow added to the Standard toolbar. A simple click on it will show a fix range of columns (three in the example) at the left and the group of columns of a chosen length (ten in the example) containing the currently focused cell, and hide all the other columns.
With a Shift+Click you get the next group to the right, and with Ctrl+Click the one to the left.
I did it just for fun and will not use it myself.

Of course, you need to permit macros to try the example. But dont permit them without first studying them to make sure they don't contain malign code.

If you actually want to use the group of macros, you should move the moduels to your local Standard container, and re-adjust the assignment to the clickable text.

more