clear & select macro

[david graham]

I need a code that that will clear the visible, unlocked cells in the scroll area in col g.

I need another code that will frame (select) the upper most, visible, unlocked cell in the scroll area in col g.

 

[Peter_SSs]

I've just realised that you did mention separate codes, whereas I've put it all in one. Hopefully you can extract what you want out of this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VisUnlocked()<br>    <SPAN style="color:#00007F">Dim</SPAN> g <SPAN style="color:#00007F">As</SPAN> Range, v <SPAN style="color:#00007F">As</SPAN> Range, vu <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> g = Intersect(Range(ActiveSheet.ScrollArea), Columns("G"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> g <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> v = g.SpecialCells(xlCellTypeVisible)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> v <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> v<br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c.Locked <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> vu <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                        <SPAN style="color:#00007F">Set</SPAN> vu = c<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        <SPAN style="color:#00007F">Set</SPAN> vu = Union(vu, c)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> c<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> vu <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> vu<br>                    .ClearContents<br>                    .Cells(1).Select<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                MsgBox "No visible, unlocked cells in scroll area in col G"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "No visible cells in scroll area in col G"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "Scroll area does not intersect with col G"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[david graham]

Peter, im going to mess around with your code a little and see if it does what i want.

The actual problem i am having is with the code below. It is supposed to:
1 - unprotect the sheet (if it was protected to begin with)
2- clear the contents of the cells in the col defined by sheet points.
3- select the upper most unlocked cell in the scroll area
4- protect the sheet if it was protected to begin with.

The problem occurs when i try to run the code on a protected sheet. Everything works fine, except that the upper most unlocked cell in the scroll area gets selected but is not framed (you cant tell that it is selected).

I can click on different unlocked cells on the sheet & they do not become framed either (i know they are selected because when i type characters appear in the cells)

This problem is driving me crazy and taking up alot of time. Seems like it should be an easy thing to fix, but im having trouble getting a response on this question. The current thread is an effort to fix this problem (think maybe the problem is in the CELL CURSOR SELECT part of the code.

Do you have any idea what the problem could be or how i could go about resolving it? thanks

  Sub CLEAR11111()
'
' CLEAR TOPOUT
'
'UNPROTECT
    Dim IsProtected As Boolean
    IsProtected = ActiveSheet.ProtectContents
     
    If IsProtected Then
    ActiveSheet.Unprotect
    End If
'CLEAR
    Dim StartRow&, EndRow&
    With Range(ActiveSheet.ScrollArea)
    StartRow = .Row: EndRow = .Rows.Count + .Row - 1
    End With
    Range(Cells(StartRow, (Sheets("POINTS").Range("EI108").Value)), Cells(EndRow, (Sheets("POINTS").Range("EI109").Value))).ClearContents
'CELL CURSOR SELECT
    Dim CRng As Object, Cell As Object
    Set CRng = Application.Intersect(Range(ActiveSheet.ScrollArea), Columns([POINTs!ei108]))
    CRng.Select
    For Each Cell In CRng
    If Cell.Locked = False Then
    Cell.Select
    Exit Sub
    End If
'PROTECT
    If IsProtected Then
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    End If
    Next Cell
    End Sub

 

[david graham]

Your code works great, thanks alot