Using Go to Special to select all balnk cells including merged cells

[dakota727]

Does anyone know of an easy way to select all empty cells in a worksheet using VBA. I wanted to take a protected sheet and use VBA to unprotect it. Then unlock all the cells on the sheet that are empty and then re-protect the sheet. I was using the Go To Special feature but found out it will not identify merged cells as containing values. The plan was to use this to lock down entries as they are mad in a log sheet. I planned to have the user use a command button to lock the entries using a button to lock and save the file. The other option is to take all cells that are not empty and change them to locked. Either direction works I gut thought the Go to Special Blanks would be a relatively easy way. Sadly that was hours ago.

It seems like what ever I try runs into issues with merged cells.

I know the easy answer is to get rid of the merged cells but I was hoping to get code that would work on a wide variety of forms.

Does anyone have any ideas?

 

[mrshl9898]

Maybe this?

Sub checkcells()

ActiveSheet.Unprotect Password:="xx"

For Each Rng In Range("A1:B4")
cellcheck1 = 0
cellcheck2 = 0

    If Rng.Value = "" Then
    cellcheck1 = 1
    End If

    Rng.Select
    If ActiveCell.MergeArea.Cells.Count = 1 Then
    cellcheck2 = 1
    End If
    
    If cellcheck1 = 1 And cellcheck2 = 1 Then
    Rng.Locked = False
    End If
Next Rng

ActiveSheet.Protect Password:="xx"

End Sub

 

[dakota727]

Thanks for the quick response.
It is close but still not discerning merged cells. Starting with all the cells locked it still will not unlock a merged cell that is empty. I tested it with a range of A1:D10 and put in a couple of merged cells. One merged cell that was empty and one merged cell that had some text. After running the routine above, everything works as expected except the empty merged cell remains locked.

 

[mrshl9898]

Yeah, wasn't sure how you'd want that handled.... I guess you'd need to count the merged cells and check if the other cells are also blank...

If A1 and B1 are merged then B1 will always be blank (as i'm sure you're aware)