Find last hidden column

Question

I am looking for a way to find last hidden column in an Activesheet. In case of rows, I can use .UsedRange to loop through all used rows backwards and find if specific row is hidden or not.

I cannot do the same in case of columns as hiding columns does not change UsedRange property (it does in case of rows).

Can someone help me how to do it, apart from looping through ALL columns, which is inefficient and extremely time consuming?

I have tried to use .UsedRange property but it does not work with columns correctly.

Answer 1

• Hidden columns do not change UsedRange.

Option Explicit

Sub LastColumn()
    Dim visRng As Range, c As Range, i As Long, LastCol As Long
    Set c = Cells(1, Columns.Count)
    If c.EntireColumn.Hidden Then
        LastCol = Columns.Count
    Else
        Set visRng = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
        Set c = visRng.Areas(visRng.Areas.Count)
        LastCol = c.Column - 1
    End If
    If LastCol > 0 Then
        Debug.Print "The last column index is " & LastCol
        Debug.Print "The last column name is " & Split(Cells(1, LastCol).Address, "$")(1)
    Else
        Debug.Print "No hidden columns"
    End If
End Sub

Microsoft documentation:

Range.SpecialCells method (Excel)