Next Unlocked Blank Cell

[DavCode]

I am attempting to remove highlighted ranges in ThisWorkbook after carrying out copy/paste operations between two workbooks. I am pasting to ThisWorkbook.
The copy workbook ranges are not relevant for this purpose.

My approach is as follows:
(Running it selects the first unlocked cell as required, but not the first blank unlocked cell. Any assistance is welcome!)

Private Sub Select_Next_Unlocked_Blank_Cell()
'Use this to prevent cell highlight/shadow overhangs (from copy/pastes)
On Error Resume Next
Application.FindFormat.Locked = False

Dim rng As Range
Set rng = ActiveSheet.Cells.Find(What:="", After:=Range("a1"), LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlNext, SearchFormat:=True)
rng.Select
End Sub

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Sub Select_Next_Unlocked_Blank_Cell()
  Dim c As Range
  For Each c In Range("A1", Range("A" & Rows.Count).End(3)(2))
    If c.Value = "" And c.Locked = False Then
      c.Select
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[DavCode]

Thanks Dante. I copied and pasted the code as you provided, but to no avail.
The selection did not move from the per-selected locked cell.

 

[DanteAmor]

The selection did not move from the per-selected locked cell.

Does the above mean that you are in an empty cell and it unlocks and you want the macro to go to the next one?

If the above is correct, then try the following:

Sub Select_Next_Unlocked_Blank_Cell()
  Dim i As Long
  i = ActiveCell.Row + 1
  Do While True
    If Range("A" & i).Value = "" And Range("A" & i).Locked = False Then
      Range("A" & i).Select
      Exit Do
    End If
    i = i + 1
    If i = Rows.Count Then Exit Do
  Loop
End Sub

Note:
The macro goes through all the cells in the column until it finds an unlocked and empty cell, if the macro doesn't find the cell then the cursor doesn't move.


If not, then explain a sequence of steps of what you do on the sheet and what the macro should do.

 

[DavCode]

This one works, but probably takes too long:

Sub Macro1()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Cells
If IsEmpty(cell) = True And cell.Locked = False Then cell.Select: Exit For
Next cell
End Sub

 

[DavCode]

Sorry - i didn't see your 2nd reply before sending the alternative code I found.

In answer to your question, I run a macro which copies and pastes ranges from one book to another. After it finishes, the pasted cell ranges remain 'selected'.

The code I am seeking (to add to the end of the copy/paste macro) is simply intended to 'deselect' those selected cells on the active sheet. The selected cells are always in a contiguous group (together), but their location is varied, depending on which sheet is active, so i'm not sure that a 'column search' approach will work. Sorry for any confusion!

 

[DavCode]

This modification looks like it speeds things up..assuming I can enure a blank unlocked cell exists within the a1:h50 range on all relevant sheets. Thank you for your help!!

Sub Macro1()
Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("a1:h50")
For Each cell In rng
If IsEmpty(cell) = True And cell.Locked = False Then cell.Select: Exit For
Next cell
End Sub