Sharing: Getting Visible Cells on Protected Sheet, VBA

[Jeffrey Mahoney]

I haven't seen an answer to this issue yet so I'm posting my solution. I have no question, but if someone has a better option, please let me know.

Problem: Getting a range of visible cells using the SpecialCells function gives an error on protected sheets. The option of unlocking the sheet is sometimes not an option.

'Normal returns a range if columns A through G are visible
Dim Sht as Worksheet
Dim R as Range
Set R = Intersect(Sht.Cells.SpecialCells(xlVisible), Sht.Range("A1:G1"))

Instead, I created a UDF to look at all the columns' visibility. The function below took my computer .07 seconds to return a range of 15 visible columns.

Sub test()
  Dim t As Double

  t = Timer
 
  Debug.Print GetVisibleColumns(ActiveSheet).Address
  Debug.Print Timer - t

End Sub

Function GetVisibleColumns(Sht As Worksheet) As Range

  Dim u As Range
  Dim Cel As Range
 
  For Each Cel In Sht.Range("1:1")
    If Cel.EntireColumn.Hidden = False Then
      If Not u Is Nothing Then
        Set u = Union(u, Cel.EntireColumn)
      Else
        Set u = Cel.EntireColumn
      End If
    End If
  Next Cel
  If Not u Is Nothing Then Set GetVisibleColumns = u
 
End Function