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

Have you ever had the pleasure of being given a workbook and when you open it it spits out the error:

Workbook contains one or more links that connot be updated.

To change the source of links, or attempt to update values again, click Edit Links.
To leave the links as is, click Continue

What really is annoying is that MS give the user no way to determine which cells are using the linked data, so no way for you to determine if and what to do! (Another poorly implemented aspect of an MS program). Below is a simple procedure that will create a worksheet where it will list each cell in each worksheet that used Linked Data as well as the source of the linked data.

Sub ShowAllLinksInfo()
'Original Author:        JLLatham
'Purpose:       Identify which cells in which worksheets are using Linked Data
'Requirements:  requires a worksheet to be added to the workbook and named LinksList
'Modified From: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/workbook-links-cannot-be-updated/b8242469-ec57-e011-8dfc-68b599b31bf5?page=1&tm=1301177444768
    Dim aLinks           As Variant
    Dim i                As Integer
    Dim Ws               As Worksheet
    Dim anyWS            As Worksheet
    Dim anyCell          As Range
    Dim reportWS         As Worksheet
    Dim nextReportRow    As Long
    Dim shtName          As String
    Dim bWsExists        As Boolean 

    shtName = "LinksList"

    'Create the result sheet if one does not already exist
    For Each Ws In Application.Worksheets
        If Ws.Name = shtName Then bWsExists = True
    Next Ws
    If bWsExists = False Then
        Application.DisplayAlerts = False
        Set Ws = ActiveWorkbook.Worksheets.Add(Type:=xlWorksheet)
        Ws.Name = shtName
        Ws.Select
        Ws.Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        Application.DisplayAlerts = True
    End If

    'Now start looking of linked data cells
    Set reportWS = ThisWorkbook.Worksheets(shtName)
    reportWS.Cells.Clear
    reportWS.Range("A1") = "Worksheet"
    reportWS.Range("B1") = "Cell"
    reportWS.Range("C1") = "Formula"

    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        'there are links somewhere in the workbook
        For Each anyWS In ThisWorkbook.Worksheets
            If anyWS.Name <> reportWS.Name Then
                For Each anyCell In anyWS.UsedRange
                    If anyCell.HasFormula Then
                        If InStr(anyCell.Formula, "[") > 0 Then
                            nextReportRow = reportWS.Range("A" & Rows.Count).End(xlUp).Row + 1
                            reportWS.Range("A" & nextReportRow) = anyWS.Name
                            reportWS.Range("B" & nextReportRow) = anyCell.Address
                            reportWS.Range("C" & nextReportRow) = "'" & anyCell.Formula
                        End If
                    End If
                Next    ' end anyCell loop
            End If
        Next    ' end anyWS loop
    Else
        MsgBox "No links to Excel worksheets detected."
    End If
    'housekeeping
    Set reportWS = Nothing
    Set Ws = Nothing
End Sub

I can’t take credit for this procedure, but thought it was the type of thing that could help a lot of people. The original source/post is provided should you wish to check it out further.