Results 1 to 10 of 10

Thread: Macro to merge cells

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to merge cells

    Hi all
    I would like to enter a value in a cell and have the 2 cells above and the 2 cells below all merge and increase the font size to 24 in bold.

    Example:
    A value is entered in A3.
    A1:A5 is merged.
    Font size increases to 24 in bold.

    Possible?

    Thank you!

    Russ
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  2. #2
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to merge cells

    Oh!
    And also undo when a value is removed.
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to merge cells

    In the sheet module try

    Code:
    Private Sub Worksheet_change(ByVal target As Range)
    If Range("A3").Value <> "" Then
        With Range("A1:A5")
            .MergeCells = True
            .Font.Size = 24
        End With
    Else
        With Range("A1:A5")
        .Font.Size = 11
        .MergeCells = False
        End With
    End If
    End Sub
    Last edited by Michael M; May 14th, 2019 at 08:57 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to merge cells

    That's cool!
    Would it be possible for it to work anywhere in column A and more than once?
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to merge cells

    Maybe this

    Code:
    Private Sub Worksheet_change(ByVal target As Range)
    If Intersect(target, Range("A:A")) Is Nothing Then Exit Sub
    If target.Cells(1).Value <> "" Then
        With Range(Cells(target.Row - 2, 1), Cells(target.Row + 2, 1))
            .MergeCells = True
            .Font.Size = 24
        End With
    Else
        With target.Cells(1)
        .Font.Size = 11
        .MergeCells = False
        End With
    End If
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to merge cells

    So close!
    If I enter a value in A3 and then delete it, the cells unmerge and the target cell becomes A1. A2:A4 do not return to the font size.

    In the Else code, can you include the 4 rows below the target cell?

    Thank you!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to merge cells

    This then....

    Code:
    Private Sub Worksheet_change(ByVal target As Range)
    If Intersect(target, Range("A:A")) Is Nothing Then Exit Sub
    If target.Cells(1).Value <> "" Then
        With Range(Cells(target.Row - 2, 1), Cells(target.Row + 2, 1))
            .MergeCells = True
            .Font.Size = 24
        End With
    Else
        target.Cells(1).UnMerge
        Range(Cells(target.Row, 1), Cells(target.Row + 4, 1)).Font.Size = 11
    End If
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to merge cells

    That works!

    This is very cool

    Thank you very much Michael!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to merge cells

    Glad to help...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to merge cells

    Hello @Michael M,

    I've seen the code you proposed, would you know how to adapt it to the values of a listbox ?

    Here is the code that displays all the items of a listbox in the column B of the sheet 9 of an excel spreadsheet

    Code:
    For b = 0 To ListBox1.ListCount - 1
    With Cells(b + 3, 2)
            .ColumnWidth = 15
            .Value = ListBox1.List(b)
    End With
    Next
    And what I would like to do in the column A is to make appear the value of a textbox in merge cells in function of the number of items of the listbox 1 that are displayed in the column B

    Any ideas ?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •