Clear merged cell value

[ALIIG]

Good day...
I am trying to clear the content of a few merged cells based on the value from another cell
Eg. if Value of Cell e.g. A11 gets the value 1
Some cells needs get to be cleared
and if the Cleel value of A11 = 2 then
Some other cells need to be cleared...
The cell values which needs to be cleared are merged cell ... Request your assistance ... Thank you

 

[Joe4]

Welcome to the Board!

The cells that are merged that need to be cleared, can you tell us the exact size of the merging?
What cells are merged?

Are the values you want to clear hard-coded or formulas?
If formulas, what does the formula look like?

Note that native Excel formulas can only returned values to the cells that they are located in. The cannot update "other" cells.
If the values in the cells you want to clear are formulas, then we can write it right into the formula to return the value you want if another cell has some value, otherwise return nothing.
If the values in the cells you want to clear are hard-coded values, then that will require VBA to clear them.

 

[ALIIG]

Good morning...
Initially, I thank you for sparing your valuable time in responding to my concern.
As per your question, I would like to let you know that:
The cells are normal without any formula
The idea is that I am creating Admin Form which has options for e.g.
Option-1 : Resigning, Termination .... I mean the End of service etc
Option-2 : Leave Applications etc
Option-3 : Loan .... and so on

Therefore if Cell AJ11 gets the value ... I want to clear some cells based on the AJ11 value which i stated above as Options


Each cell width is 2

Merging Cells
1. R18:X18
2. F29:I30
3. J29:M30

I hope I am able to provide appropriate feedback.

So I need support in VBA Coding to clear the cells based on AJ11 value.
Thank you,

 

[ALIIG]

Good morning...
Initially, I thank you for sparing your valuable time in responding to my concern.
As per your question, I would like to let you know that:
The cells are normal without any formula
The idea is that I am creating Admin Form which has options for e.g.
Option-1 : Resigning, Termination .... I mean the End of service etc
Option-2 : Leave Applications etc
Option-3 : Loan .... and so on

Therefore if Cell AJ11 gets the value ... I want to clear some cells based on the AJ11 value which i stated above as Options


Each cell width is 2

Merging Cells
1. R18:X18
2. F29:I30
3. J29:M30

I hope I am able to provide appropriate feedback.

So I need support in VBA Coding to clear the cells based on AJ11 value.
Thank you,

 

[Peter_SSs]

See if you can adapt this to fit your exact circumstances.

Select Case LCase(Range("AJ11").Value)
  Case "resigned", "terminated"
    Range("R18").Value = ""
  Case "leave"
    Range("F29").Value = ""
  Case "loan"
    Range("J29").Value = ""
End Select

 

[Joe4]

Assuming that you want this to run automatically upon entering a value into AJ11, right-click on the Sheet tab name. select "View Code", and paste this into the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if cell AJ11 was just updated
    If Target.Address(0, 0) = "AJ11" Then
        Application.EnableEvents = False
'       See what AJ11 was updated to
        Select Case Target.Value
            Case "Option-1"
                Range("R18:X18").ClearContents
            Case "Option-2"
                Range("F29:I30").ClearContents
            Case "Option-3"
                Range("J29:M30").ClearContents
        End Select
        Application.EnableEvents = True
    End If
    
End Sub

This should run automatically as someone manually updates AJ11.

Note my lines like this:

            Case "Option-1"

You will need to update what is in between the double-quotes to exactly match the values that you are looking for. Do this for all 3 Case statements.
Peter shows above how you can enter multiple values into a single Case statement.

 

[ALIIG]

Good day...
Dear Joe,
Once again I thank you so much for putting your efforts, giving your valuable time in trying to render support.
I have copied the codes, but this time there is no error, but at the same time there is no reaction, i mean none of the cell is getting cleared although the options are changed.

I would like to add one more thing...

The Cell AJ11 is changed based on the selection of value in Cell U11
For example
If U11= TERMINATION or RESIGNATION or RETIREMENT or ETC THEN AJ11 gets the value like 1 or 2 or 3 or 4 ..... etc

The formula in the cell U11 is as below
=IF(U11="",0,VLOOKUP(U11,Sheet1!A3:B17,2,FALSE))

Please advise
Thank you,

 

[Joe4]

OK, that is an important fact we needed to know about. Worksheet_Change event procedure VBA code is not triggered by changes in formulas, only manual updates.
We can tell our code to run whenever cell U11 is manually updated, but look at the value in AJ11, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if cell U11 was just updated
    If Target.Address(0, 0) = "U11" Then
        Application.EnableEvents = False
'       See what AJ11 was updated to
        Select Case Range("AJ11").Value
            Case "Option-1"
                Range("R18:X18").ClearContents
            Case "Option-2"
                Range("F29:I30").ClearContents
            Case "Option-3"
                Range("J29:M30").ClearContents
        End Select
        Application.EnableEvents = True
    End If
    
End Sub

 

[ALIIG]

Good morning...
Dear Mr. Joe,
I understood your explanation, but that's the way it's required because the user will not do the manual entry.
Could you suggest any other way which can be done....

Instead, if we can refer to any blank cell of the sheet... like for example AZ1 which we keep blank, so

Select case Range ("AJ11").value
Case 1
Range("R18:X18") = AZ1
Case 2
Range("F29:I30") = AZ1
Case 3
and so on...
End select

I know it sounds stupid but just a guess....
Meanwhile, I will try from my end and see do it has any effect
However, I appreciate all your efforts and time towards thread.
Thank you so much
Best regards

 

[Peter_SSs]

the user will not do the manual entry.

How does the entry get made in cell U11? You said a 'selection' was made there. That sounds like it would trigger the Worksheet_Change code. Did you try entering Joe's suggested code and then making an entry in cell U11?