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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alisonthomAuthor Commented:
Thank you so much gowflow!  That is exactly what I was looking for.

Thanks again
Alison
0
gowflowCommented:
Your welcome.
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.