VBA code and merged cells

[jdluke6545]

I am needing a VBA code to unlock cells but when looking for an answer I came across a thread that said VBA and merged cells don't work.
Well I have merged cells and there is no way I can redo the sheet and not have merged cells.
is there any way to get VBA code to play nice with merged cells?
I can post images of my sheet that I need help with VBA code If anyone has a possible solution to this problem first.
Or I can try the XL2BB thing.... never used that before tho....

 

[jdluke6545]

Thanks Guys!!
I will give these a try and report back as soon as I can. It may be tomorrow tho...

 

[Alex Blakenburg]

Thanks for the update.

 

[Michael M]

@Michael M, you just snuck in before me. Let me know if I haven't gotten this right.

@jdluke6545
See if this does the job.
The sheet will need to be unprotected first.

Sub FindAndUnlockRelated()

    Dim searchRng As Range
    Dim ws As Worksheet
    Dim findStr As String
    Dim foundCell As Range
    Dim firstMatch As String
    Dim findArr() As String
    Dim i As Long
   
    findArr = Split("NT,ST", ",")

    Set ws = ActiveSheet
   
    Set searchRng = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
   
    For i = LBound(findArr) To UBound(findArr)
        findStr = Trim(findArr(i))
        Set foundCell = Cells.Find(what:=findStr, LookIn:=xlFormulas, MatchCase:=False, lookat:=xlWhole)
        firstMatch = foundCell.Address
       
        Do While Not foundCell Is Nothing
            Set foundCell = Cells.FindNext(foundCell)
            foundCell.Offset(0, 6).MergeArea.Locked = False
            If firstMatch = foundCell.Address Then Exit Do   
        Loop

    Next i
End Sub

XL2BB of the sample data

20210731 VBA Merged Cells Locked Cells and Identification.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	ID #						Description
2	55						This cell to remain locked
3							This cell to remain locked
4	NT						This cell to remain UNLOCKED
5	22						This cell to remain locked
6	ST						This cell to remain UNLOCKED
7							This cell to remain locked
8	55						This cell to remain locked
9							This cell to remain locked
10	NT						This cell to remain UNLOCKED
11	22						This cell to remain locked
12	ST						This cell to remain UNLOCKED
13
Data

Just finished golf....and cracking a Shiraz....mainly to drown my sorrows !!

 

[jdluke6545]

So,, neither of those worked.
Could we sorta forget about the fact that the cells are merged (because excel uses the furthest left cell of a merged group for referencing data) and try a simple IF statement, something like this:
IF A CELL IN THE RANGE OF (A1:A35) = ST or NT THEN
SELECT THE CELL THAT IS OFFSET (0,6) FROM THE THAT TARGET CELL and use something like .LOCKED=FALSE
Does this have any close chance of possibly working? If so, how would I write this?
I did record a macro on the same sheet with these merged cells where I selected a select bunch of cells and cleared the data. I then Linked that macro to a button and it works.
So, that kinda tells me that there must be a way for this to work.
I can upload that code if it would help...??
But like I said, that code just deletes the data so I can start with a clean sheet instead of spending 30 minutes manually selecting the all the pertinent cells and deleting.

 

[Michael M]

This might get us started

Sub MM1()
 Dim r As Long
 For r = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(r, 1).Value = "ST" Or Cells(r, 1).Value = "NT" Then
        Cells(r, 2).MergeArea.Locked = False
    End If
Next r
End Sub

Ok, Is the column with ST or NT column "A" ?
Is ST or NT the only text in the cell ?....no spaces, special characters, etc.
If the merge area to be unlocked starts at any other colu other than "B", my code won't work......
Change the 2 in this line to whatever column is the first one in the merged aresa

 Cells(r, 2).MergeArea.Locked = False

 

[jdluke6545]

VBA Unlock Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
3	ID #						DESCRIPTION																																					QTY					COST
4
5	55						THIS CELL TO REMAIN LOCKED
6							THIS CELL TO REMAIN LOCKED
7	NT						THIS CELL SHOULD REVERT TO UNLOCKED
8	22						THIS CELL TO REMAIN LOCKED
9	ST						THIS CELL SHOULD REVERT TO UNLOCKED
10							THIS CELL TO REMAIN LOCKED
11							THIS CELL TO REMAIN LOCKED
12							THIS CELL TO REMAIN LOCKED
13							THIS CELL TO REMAIN LOCKED
14							THIS CELL TO REMAIN LOCKED
15							THIS CELL TO REMAIN LOCKED
16							THIS CELL TO REMAIN LOCKED
17							THIS CELL TO REMAIN LOCKED
18							THIS CELL TO REMAIN LOCKED
19							THIS CELL TO REMAIN LOCKED
20							THIS CELL TO REMAIN LOCKED
21							THIS CELL TO REMAIN LOCKED
22							THIS CELL TO REMAIN LOCKED
23							THIS CELL TO REMAIN LOCKED
24							THIS CELL TO REMAIN LOCKED
25							THIS CELL TO REMAIN LOCKED
26							THIS CELL TO REMAIN LOCKED
27							THIS CELL TO REMAIN LOCKED
28							THIS CELL TO REMAIN LOCKED
29							THIS CELL TO REMAIN LOCKED
30							THIS CELL TO REMAIN LOCKED
31							THIS CELL TO REMAIN LOCKED
32							THIS CELL TO REMAIN LOCKED
33							THIS CELL TO REMAIN LOCKED
34
TEST SHEET

 

[jdluke6545]

That post above was using the XL2BB format. for whatever it was worth.

 

[antman2988]

Short answer is NO....VBA and merged cells are kryptonite in Excel.
Merged cells should have been removed from Excel at birth.
Another option to merged cells is.....Highlight the cells >>RClick>>Format cells>>Alignment tab>>Horizontal Dropdownbox>>Center across selection

I don't typically use merged cells, but this is good to know.

 

[jdluke6545]

@Michael M
Yes, the MERGED cells that will have only the letters NT or ST in them and does start in column A and is merged thru column F. But it does start in column A.
The letters do not and will not contain any spaces or other characters.
BUT the merged cells needing to be unlocked starts in cell G and is merged thru AL.
So I need to count the number of columns that COLUMN AL is and put that number in place of the 2?

 

[Michael M]

No you need to change the 2 to a 7