Hide/unhide columns macro

[jkopedia]

I am working on an analysis spreadsheet which allows for multiple properties to be analysed or for a single property to be analysed and as part of this I have several areas where there is the option to open up additional columns for properties if needed by the user. I have a macro to unhide these columns in order from property 1 to 10, however I am looking for a macro that will allow me to hide these columns from property 10 to 1 (reverse order), but can't seem to get it right. If I reverse my unhide macro it will hide them from 1 to 10, not 10 to 1 so I'm a little stumped how to get the format I want.

Can anyone help with this? My unhide macro is below if of any use for context; Essentially, this one is unhiding the columns from L to FE, but I would like the hide function to go from FE to L
__________________________
Sub UnhideACol()
Dim HiddenRange As Range, c As Range
Dim i As Integer
Set HiddenRange = Range("L6:FE74")
For i = 5 To 1 Step -1
For Each c In HiddenRange
If c.EntireColumn.Hidden = True Then
c.EntireColumn.Hidden = False
Exit For
End If
Next c
Next
End Sub

 

[DanteAmor]

Could you explain with an example, that is, properties 1 to 10 are a condition to hide and show columns?
Because hiding the columns of the L to the FE is the same as if the hidden ones of the FE to the L, in the end all those columns will be hidden.

Sub Hide_Col()
    Range("L:FE").EntireColumn.Hidden = True
End Sub
'
Sub Unhide_Col()
    Range("L:FE").EntireColumn.Hidden = False
End Sub

I hope your comments

 

[My Aswer Is This]

If your wanting to toggle columns F to FE from hidden to unhidden
Try this:

Sub Hide_Unhide_Columns()
'Modified 1/3/2019 11:44:57 PM  EST
Application.ScreenUpdating = False
Columns("L:FE").Hidden = Not Columns("L:FE").Hidden
Application.ScreenUpdating = True
End Sub

 

[jkopedia]

True, but I am looking to have them hidden one column at a time so that users can add columns for properties from left to right and if they later need to take them away (or add one too many in error), then they can hide them one at a time from right to left. For example, If I have four property columns open (1, 2, 3, 4) and I want to remove one (#4 in this case), my current format will not do that (it will remove #2 first, so I am left with 1, 3, 4 instead of the intended 1, 2, 3). Is there a way to set a macro so that it will take the right most column and not the leftmost column?

 

[Fluff]

How about

Sub jkopedia()
   Columns(Range("L:FF").SpecialCells(xlVisible)(1).Offset(, -1).column).Hidden = False
End Sub

 

[DanteAmor]

I'm confused, we're talking about hiding or removing a column. It does not matter if you hide them from left to right or from right to left, the "E" column will always be the "E" column. If you want to delete a column just refer to the column "E":

Columns ("E"). Delete

it does not matter if the column "E" is hidden or visible.


Now if you want to delete the last hidden column, you must first find the last column and then find the first hidden column from right to left; then eliminate it, it could be like this:

Sub remove_last_column_hide()
    uc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    For j = uc To 1 Step -1
        If Columns(j).Hidden = True Then
            Columns(j).Delete
            Exit For
        End If
    Next
End Sub

 

[jkopedia]

Thanks for the posts DenteAmor; the purpose of the s/sheet is to allow a user to input variables for one or multiple properties up to a max of 31 properties. Each property has five columns assigned to it. As there are a substantial amount of coded cells in the analysis of each property going down the column sets, it was easier to hide all the pre-populated property columns and have an unhide/hide macro in a + and - button on the page, rather than programming a macro to insert all the formulas into a new set of columns. My macro for unhiding the columns works great and unhides the column sets from left to right on the page as wanted. However, I am looking for a macro to hide the column sets from right to left on the page (opposite to the unhide direction); not looking to delete the columns, just re-hide them. Maybe it is not possible to go in that direction and can only hide column sets from left to right? The reason for the direction of hiding the column sets is that our presentation of properties always goes left to right on the s/sheet so when properties are removed it is from the right and not the left, thus to automate the function it would be ideal for the macro to go in that direction.

Thanks as well for your post Fluff; I tried your suggestions, but it was not what I was looking for. Appreciate the suggestions though!

 

[My Aswer Is This]

Sure the script can be run to hide or unhide from left to right or right to left.

But your script should only take about .5 seconds to run

So if we write a script to go in the opposite direction it will only take maybe .5 seconds to run.

So here is your script running in opposite direction

Sub UnhideACol()
'Modified  1/4/2019  3:02:52 PM  EST
Dim HiddenRange As Range, c As Range
Dim i As Integer
Set HiddenRange = Range("L6:FE74")
For i = 1 To 5
For Each c In HiddenRange
If c.EntireColumn.Hidden = False Then
c.EntireColumn.Hidden = True
Exit For
End If
Next c
Next
End Sub

So why do you care if it runs from left to right or right to left.

This happens so fast you will not see it happen you will just see the results.

 

[jkopedia]

Sorry if some miscommunication; as the function is performed, you are correct in that I don't care how the direction goes, however each time I hit the function I would like the set of 5 farthest to the right on the s/sheet to close first, not the set of five on the left of the spreadsheet.

 

[Fluff]

How about

Sub jkopedia()
   With Range("L:FE").SpecialCells(xlVisible)
      .Offset(, .Columns.Count - 5).Resize(, 5).EntireColumn.Hidden = True
   End With
End Sub