Merge 4 cells into 1 depending on another cells value

**crazytimechris** · 09-16-2012, 07:48 AM

Good afternoon,

I am looking for a way to merge 4 cells depending on a cell value. I will explain. I am creating a spreadsheet where I will be keeping track of peoples payments for a class. They can pay either weekly or monthly. for example:

If A1 says "Monthly" I want B1:E1 to merge into one cell. If A1 says "Weekly" i want B1:E1 to be 4 separate cells. I am expecting this to be in vba and have a little experience but need a point in the right direction...

Thank you in advance.

Chris

**Palmetto** · 09-16-2012, 08:17 AM

I don't recommend the use of meregd cells for data sheets - most knowledgeable users avoid them as much as possible.
It is better, IMO, to just leave the cells unmerged. You already flag the payment as weekly or monthly. Why the need to merge cells?

However, give this code a try. It uses the worksheet change event to merge/unmerge cells in columns B:E when cells in column-A are changed to Monthly / Weekly.

This code must go into the worksheet module: right click sheet tab > View Code > copy & paste code into the VB Editor then close it.
If using Excel 2007 you must save to a .xlsm file type.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then 'adjust range reference to suit
        Select Case Target.Value
            Case "Monthly"
                Range("B" & Target.Row & ":E" & Target.Row).Merge
            Case "Weekly"
                Range("B" & Target.Row & ":E" & Target.Row).UnMerge
        End Select
    End If

End Sub

**crazytimechris** · 09-16-2012, 10:09 AM

Thank you very much for your help. I totally understand what you mean about merging cells as I have already run into an issue!

I am trying to populate the cells depending on the weekly/montly as well and am getting stuck. If weekly I want the range of cells to show 6 and of monthly I want the 1 to show 20. I have uploaded my sheet for you to look at if you /anyone can help me!

Thank you!

ChrisThrowdown.xls

**watersev** · 09-16-2012, 01:07 PM

hi crazytimechris, please check attachment, try to change D column values within D1:D19. The code works basing on the month name as a merged header. Changing value results in changes for all months for now.