Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,130
- Office Version
- 365
- Platform
- Windows
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.
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.
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.
VBA Code:
'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.
VBA Code:
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