Finding first cell

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Could someone please advise how to bring the first cell to view (using VBA)
when the following conditions are in play.
1. The first cell to view starts at row 26
2. Row 25 and above are frozen and locked
3. Some of the rows/cells below 25 may also be locked
4. All locked cells are set as unselectable
 
I locked some cells in a test worksheet and then protected it in code:

Option Explicit
Sub testme01()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.EnableSelection = xlUnlockedCells
.Protect Password:="hi"
End With

End Sub

Then I could do something like this. Just keep selecting until I found a cell
that didn't cause an error.

Sub testme02()

Dim iRow As Long

iRow = 25
On Error Resume Next
Do
Application.Goto ActiveSheet.Cells(iRow, "A")
If Err.Number = 0 Then
Exit Do
Else
Err.Clear
iRow = iRow + 1
'keep going
End If
Loop
On Error Goto 0
MsgBox "I'm in: " & ActiveCell.Address

End Sub
 
Yu'rve done it again Dave, though I can't work out why it needs to do the
loop process to get to that cell. I just made a couple of minor changes and
then it worked first time.
Thanks!
Rob
 
As I said in my other post, Dave, it works fine, except......
I omitted to say that I wanted to find the top cell when the rows below 25
is in filter mode.
I don't want to remove the filter, just get the screen to show the top row
of the filtered data. It's not even necessary to activate the cell if that
is a problem. Just want the first row in the filtered data to be the first
row showing under the headings.
Sorry!!!
Rob
 
You could just check to see if that row was visible and the cell was selectable:

Sub testme03()

Dim iRow As Long

iRow = 25
On Error Resume Next
Do
Application.Goto ActiveSheet.Cells(iRow, "A")
iRow = iRow + 1
If Err.Number = 0 Then
If ActiveCell.EntireRow.Hidden = False Then
Exit Do
End If
Else
Err.Clear
'keep going
End If
Loop
On Error GoTo 0
MsgBox "I'm in: " & ActiveCell.Address

End Sub

I'm not sure how your cells are protected, but you can find that first visible
cell in the filter with something like this (stolen from another post):

Option Explicit
Sub testme()

Dim myRngF As Range

Set myRngF = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set myRngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If myRngF Is Nothing Then
MsgBox "No cell to select"
Else
myRngF.Areas(1).Cells(1, 1).Select
End If

End Sub

Change that .select to application.goto and it might work for you--If your cells
are unlocked.
 
Back
Top