Sometimes you need to loop through all the columns within a given worksheet, so you need to first ascertain what is the last column in the worksheet. So how can one do this reliably?

Well, if all you columns are visible, then you can use code such as:

Dim iLastCol        As Long
iLastCol = Sheets("YourSheetName").Cells(7, Sheets("YourSheetName").Columns.Count).End(xlToLeft).Column

Or

Dim iLastCol        As Long
iLastCol = ActiveSheet.Cells(7, ActiveSheet.Columns.Count).End(xlToLeft).Column

Now that is all fine and dandy, if all your columns are visible, but what happens when you need to identify the last column even if those column may or may not be visible? Once again, no major problem. We just need to tweak our code to something like:

Dim iLastCol        As Long
iLastCol = Sheets("YourSheetName").UsedRange.Columns(Sheets("YourSheetName").UsedRange.Columns.Count).Column

Or

Dim iLastCol        As Long
iLastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

Now both can be very useful in different situations. Just beware that there is a difference depending on whether or not you want to include hidden columns in your count/loop.

In the same thought process as my previous post MS Excel – VBA – Unhide as WorkSheets in a WorkBook, below is are two simply procedures. The first will hide all the WorkSheets within the WorkBook, however they can still be made visible by the user through the standard Excel menus. The second one, hides all the WorkSheets but this time they are ‘veryhidden’, which means there is no way for the user to unhide them without using VBA to do so. Even if they use the standard menus the ‘very hidden’ sheets will not appear.

'---------------------------------------------------------------------------------------
' Procedure : hideAllWs
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Hide all the worksheets except for the active sheet
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25                 Initial Release
'---------------------------------------------------------------------------------------
Function hideAllWs()
On Error GoTo Error_Handler
    Dim WS              As Worksheet

    For Each WS In Worksheets
        If WS.Name <> ActiveSheet.Name Then WS.visible = xlSheetHidden
    Next WS

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: hideAllWs" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : VeryhideAllWs
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Hide all the worksheets except for the active sheet
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25                 Initial Release
'---------------------------------------------------------------------------------------
Function VeryhideAllWs()
On Error GoTo Error_Handler
    Dim WS              As Worksheet

    For Each WS In Worksheets
        If WS.Name <> ActiveSheet.Name Then WS.visible = xlSheetVeryHidden
    Next WS

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: VeryhideAllWs" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

You’ll notice in both routines that it will not hide the active worksheet. That is because you can’t, it will err. So you need to set focus on whatever worksheet you want to remain visible and then run it to hide all the other sheets in the workbook.

I created a monster. Well sort of. I created a security routine that controls the visibility of worksheet based on the current user. This is great, but as the developer and tester, impersonating other users, I didn’t want to have to make 70+ Worksheets visible again. Even more so since, I was using the xlSheetVeryHidden visibility property making it impossible to restore manually! So what to do. Easy, create a very simple routine to loop through all the WorkSheets of the current WorkBook and set them all visible again.

'---------------------------------------------------------------------------------------
' Procedure : UnhideAllWs
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the WorkSheets of the current WorkBook and set them all
'             to visible.
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25                 Initial Release
'---------------------------------------------------------------------------------------
Function UnhideAllWs()
On Error GoTo Error_Handler
    Dim WS              As Worksheet

    For Each WS In Worksheets
        WS.visible = xlSheetVisible
    Next WS

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf  _
            "Error Source: UnhideAllWs" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function