Excel VBA, selecting top-left cell in pane

Hi

I would appreciate help with VBA that I could use to select the top left hand cell in a pane (my worksheet has frozen panes).
I have tried the following, however this does not deal with the situation where the first column in the pane is hidden.  So if the pane starts at column G, but columns G - J are hidden I would like the selection to be made with respect to column K.  Using the VBA below the selection is made in column G.

With ActiveWindow
    .ScrollRow = 1
    .ScrollColumn = 1
    Call Cells(RowIndex:=.ScrollRow, ColumnIndex:=.ScrollColumn).Select
End With

Open in new window


Many thanks in advance
Alison
alisonthomAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
Yes you need to try this

Sub SelectTopLeft()
Dim cCell As Range

With ActiveWindow
    .ScrollRow = 1
    .ScrollColumn = 1
    Set cCell = Cells(RowIndex:=.ScrollRow, ColumnIndex:=.ScrollColumn)
    If cCell.EntireColumn.Hidden Or cCell.EntireRow.Hidden Then
        
        Do
            If cCell.EntireColumn.Hidden Then
                Set cCell = cCell.Offset(0, 1)
            End If
            
            If cCell.EntireRow.Hidden Then
                Set cCell = cCell.Offset(1, 0)
            End If
        
        Loop Until cCell.EntireColumn.Hidden = False And cCell.EntireRow.Hidden = False
        
        cCell.Select
        
    Else
        Cells(RowIndex:=.ScrollRow, ColumnIndex:=.ScrollColumn).Select
    End If
End With
End Sub

Open in new window


gowflow
0
 
alisonthomAuthor Commented:
Thank you so much gowflow!  That is exactly what I was looking for.

Thanks again
Alison
0
 
gowflowCommented:
Your welcome.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.