Turn Hide Macro into Hide/Unhide Toggle

[smas_tx]

Hi, I'm burnt out trying to figure this one out, would appreciate the help.

Below is the hide macro (which works) ->

Sub Entry_Cleanup()
Set Rng = Range("E3:CZ3")
For Each cell In Rng
If cell.Value = 0 Or cell.Value = "" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

I'm trying to get it to unhide all columns if already hidden when macro runs.


This was my attempt ->

Sub Entry_Cleanup()
Set Rng = Range("E3:CZ3")
For Each cell In Rng
If cell.Value = 0 Or cell.Value = "" Then
cell.EntireColumn.Hidden = True
End If
Next cell
If Columns("E:CZ").Hidden = True Then
Selection.EntireColumn.Hidden = False
End If
End Sub

Help please.

 

[Caleeco]

Hello Smas,

Does this do what you need?

Sub EntryClean()
Dim r As Range


For Each r In Range("E3:CZ3")
    If Columns(r.Column).Hidden = True Then
        Columns(r.Column).Hidden = False
    Else
        If r.Value = 0 Or r.Value = "" Then Columns(r.Column).Hidden = True
    End If
Next r


End Sub

Let me know
thanks

Caleeco

 

[JoeMo]

This may be faster:

Sub Entry_Cleanup()
Application.ScreenUpdating = False
Columns("E:CZ").Hidden = False
Set Rng = Range("E3:CZ3")
For Each cell In Rng
    If cell.Value = 0 Or cell.Value = "" Then cell.EntireColumn.Hidden = True
Next cell
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

Try:

Sub Entry_Cleanup_v1()

    Dim rng         As Range
    Dim rng_area    As Range
    Dim rng_hide    As Range
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Set rng_area = .Range("E3:CZ3")
        With rng_area
            .EntireColumn.Hidden = False
            .Replace 0, vbNullString
            On Error Resume Next
            Set rng_hide = .find(vbNullString, LookIn:=xlValues, lookat:=xlWhole)
            On Error GoTo 0
        End With
        
        If Not rng_hide Is Nothing Then
            For Each rng In rng_area
                With rng
                    If .Value = vbNullString Then Set rng_hide = Union(rng, rng_hide)
                End With
            Next rng
            rng_hide.EntireColumn.Hidden = True
        End If
    End With
    
    Application.ScreenUpdating = True

    Set rng_area = Nothing
    Set rng_hide = Nothing
    
End Sub

 

[smas_tx]

Thanks all, greatly appreciated.

Tried all 3, for some reason the 'unhide toggle' was only working for me on the 1st one.
I ended up taking the application.screenupdating syntax from the bottom 2 and adding it to Caleeco's code though, I like that a lot. Thanks again.

 

[Caleeco]

Glad you got it working, turning off screenupdating while the code runs is a good idea!

Caleeco

 

[JackDanIce]

May be an issue with your data, the code I suggested works on dummy data I tried testing with (using values of 0, "" and x randomly through the range with correct columns being hidden).

My code is forcing all 0 values to be "" so it's only testing for cells with value "" and then hiding those specific columns in a single operation.

JoeMo's code works for me on same test data too.