Merge & Unmerge Cells

[decent_boy]

Hi,
I want excel vba code on worksheet event my question is that i have data from g8:g28 and if g8 = 2 then unmerge a8:a10: and merge a8:a9 and if g8=3 then then unmerge a8:a10: and merge a8:a10
if g9 = 2 then unmerge a8:a10: and merge a9:a10 and if g9=3 then then unmerge a8:a10 and merge a9:a11 Similar this is with all data from g8:g28

I have developed some below code but I do not understand that how should these code can be concise and apply on cell g8 to g28

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False

Select Case [G8].Value
    Case Is = 1
        Range("A8:A10,F8:F10,G8:G10").UnMerge
    Case Is = 2
        Range("A8:A10,F8:F10,G8:G10").UnMerge
        Range("A8:A9,F8:F9,G8:G9").Merge
    Case Is = 3
        Range("A8:A10,F8:F10,G8:G10").UnMerge
        Range("A8:A10,F8:F10,G8:G10").Merge
  End Select

On Error Resume Next


Application.DisplayAlerts = True
End Sub

 

[6StringJazzer]

I am not following your question. The code you showed here will run anytime that there is a change to any cell on the worksheet. When it runs it takes action based on the value in cell G8 (no matter what cell was changed).

Please describe what you want to happen instead.

Also just about everyone here will recommend against using merged cells, especially when you are using VBA. They usually cause more problems than they solve.

 

[decent_boy]

I am not following your question. The code you showed here will run anytime that there is a change to any cell on the worksheet. When it runs it takes action based on the value in cell G8 (no matter what cell was changed).

Please describe what you want to happen instead.

Also just about everyone here will recommend against using merged cells, especially when you are using VBA. They usually cause more problems than they solve.

 

[decent_boy]

Actual I have range from G8 to G28 and i am talking about mentioned range, let me explain you that you saw code based on G8 and if G9 changes then

Select Case [G9].Value
    Case Is = 1
        Range("A9:A11",F9:F11,G9:G11").UnMerge
    Case Is = 2
        Range("A9:A11,F9:F11,G9:G11").UnMerge
        Range("A9:A10,F9:F10,G9:G10").Merge
    Case Is = 3
        Range("A9:A11,F9:F11,G9:G11").UnMerge
        Range("A9:A11,F9:F11,G9:G11").Merge
  End Select

Likewise if it is G10 to G28 if any change

 

[decent_boy]

Can anyone please help to resolve it?

 

[decent_boy]

Can anybody tell me if this can be resolved or not

 

[jolivanes]

Merge and Centre: A Better Alternative

Using Merge and Centre in Excel can cause many problems. This little gem is a fantastic alternative with no known side effects!

officemastery.com

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Excel: Don’t Merge Those Cells! Here is an Alternative. – Office Bytes

 

[6StringJazzer]

It is possible but writing the new code will take more time that I am able to offer right now. Sorry.

 

[decent_boy]

It is possible but writing the new code will take more time that I am able to offer right now. Sorry.

No problem, I really appreciate your positive reply