Hide/Unhide Toggle

[Nush1981]

Please could someone provide me with the VBA to 'toggle' between HIDING and UNHIDING columns within one macro

e.g. toggle between this hiding and unhiding the following

Sub HideColumns()

Range("G:G,I:I,AB:AV").Activate
Selection.EntireColumn.Hidden = True

End Sub

Thanks

 

[Jonmo1]

Try this

Sub HideColumns()

If Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True Then
    Range("G:G,I:I,AB:AV").EntireColumn.Hidden = False
Else: Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True
End If

End Sub

 

[Nush1981]

Great thanks

This is the full script

Sub HideColumns()

If MsgBox(prompt:="Have you protected the workbook?", _
Buttons:=vbQuestion + vbOKCancel + vbDefaultButton2, _
Title:="Hide Columns") = vbCancel Then
MsgBox "Action Cancelled"
Exit Sub
End If
Application.ScreenUpdating = False

If Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True Then
Range("G:G,I:I,AB:AV").EntireColumn.Hidden = False
Else: Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True
End If

End Sub

Do you know how to change the message when the button is pressed for the second time? I only want the message to appear when the columns are hidden

 

[mortgageman]

Try this

Sub HideColumns()

If Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True Then
    Range("G:G,I:I,AB:AV").EntireColumn.Hidden = False
Else: Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True
End If

End Sub

I'm pretty sure this will do the same thing:

Sub HideColumns2()
Range("G:G,I:I,AB:AV").EntireColumn.Hidden = Not Range("G:G,I:I,AB:AV").EntireColumn.Hidden
End Sub

 

[Jonmo1]

Should be like this

If Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True Then
     If MsgBox(prompt:="Have you protected the workbook?", _ 
          Buttons:=vbQuestion + vbOKCancel + vbDefaultButton2, _ 
          Title:="Hide Columns") = vbCancel Then 
          MsgBox "Action Cancelled" 
          Exit Sub 
     End If 
End If

Application.ScreenUpdating = False 

If Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True Then 
      Range("G:G,I:I,AB:AV").EntireColumn.Hidden = False 
Else: Range("G:G,I:I,AB:AV").EntireColumn.Hidden = True 
End If 

End Sub

 

[Jonmo1]

Gene, that's cool. But man, it makes my head spin thingking about it.

 

[Nush1981]

Perfect - thank you!!

 

[mortgageman]

Gene, that's cool. But man, it makes my head spin thingking about it.

Every trick I know, I learned on this board.
(Sorry for the duplicate post everyone)

 

[stir-crazy]

Sorry, don't mean to hijack the thread, but my issues very similar. I have a macro that will hide rows if a cell in the A column is a certain value, which works great. But I want it to toggle this unhiding, but I keep on getting errors; not sure how to resolve this. The best I've been able to do is get it to unhide, then immediately re-hide this!

Sub HideItem()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each cell In Range("A8:A207")
  If cell.Value = "xxxxx" Then cell.EntireRow.Hidden = True
Next cell
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

Thanks in advance!

 

[stir-crazy]

Or more, why do I get the "Else without If" error when I try this:

Sub HideItem()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each cell In Range("A8:A207")
    If ccell.EntireRow.Hidden = True Then cell.EntireRow.Hidden = False
    Next cell
    Else: For Each cell In Range("A8:A207")
        If cell.Value = "xxxxx" Then cell.EntireRow.Hidden = True
        Next cell
        End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub