vba go to next unlocked cell in range

Is this what you want? Paste this in a module

Sub Sample()
    Dim rng As Range
    Dim cl As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    
    For Each cl In rng.Cells
        If cl.Locked = False And Len(Trim(cl.Value)) = 0 Then
            MsgBox cl.Address & " is unlocked and unused cell"
        End If
    Next
End Sub

Select allOpen in new window

Sid

Hello,

How do I get this code to go through the range and select the cell that is [not locked] and value = 0?

I have played with the code, but it is going to the last cell in the range [G66]

Thanks

You mean this?

Sub Sample()
    Dim rng As Range
    Dim cl As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    
    For Each cl In rng.Cells
        If cl.Locked = False And Len(Trim(cl.Value)) = 0 Then
            cl.Select
            Exit For
        End If
    Next
End Sub

Select allOpen in new window

Hello SinddharthRout:

I am almost there - I just need it to scroll the column first  - instead of the row.

If I have data in C4 - It is going to D4.  I need it to scroll down to the next "free" cell in the column which would be C5 ( if C4 has data ). If Column C is complete, then it should go to column D.

Can this be done?

mike637

You mean like this?

Sub Sample()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
            Exit For
        Next i
    Next j
End Sub

Select allOpen in new window

Hello SinddharthRout:


I am getting an error message at line 9

       Next i                   statement

Compile error:  Next without For

Sorry.

Try this

Sub Sample()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
                Exit For
            End If
        Next i
    Next j
End Sub

Select allOpen in new window

Sid

Wait, I think, I just spotted another error,

Sid

Worked Excellent!!

Many Thanks.