Consulting

Results 1 to 13 of 13

Thread: How to Merge Cells Specially

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location

    How to Merge Cells Specially

    hi everyone, l am new in vba and need a favour.

    l have data type like this:

    1 a j
    1 b k
    1 c l
    2 d m
    2 e n
    2 f o

    in this sheet, the numbers in the first column are unique. the following columns may be one or more and are not unique. for example, the first column contains the student numbers and the following ones contain the lectures.

    l need a macro to merge cells according to these rules:

    1- the repeating values of the first column are merged in one cell as a single value like 1, 2, or 3...

    2- then, the following columns' values are merged not losing any data within the range of the first columns unique values.

    the result must be like this:

    a j
    1 b k
    c l

    d m
    2 e n
    f o

    abc, jkl, def and mno must be in the same cell and written over and over. l hope l have explained well.

    thanx...

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,843
    Location
    Large effort should be used to avoid merging cells in data structures.

    Is there any way to achive your needs without merging cells?
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location
    Quote Originally Posted by SamT View Post
    Large effort should be used to avoid merging cells in data structures.

    Is there any way to achive your needs without merging cells?
    my sheet is exactly as l explained in my post. let me give a more clear example

    john physics
    john maths
    kate philosophy
    kate biology
    kate sociology

    there are actually two individuals but five rows.l have to merge the unique values and the neigbour columns in the same range. unique values must be written once but the others must be protected.

    once l do it, then l can use ms access to create a new excel sheet as unmerged cells. l have no other idea to achieve my goal but of course l am open for the new suggestions.

    sincerely...

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,843
    Location
    We don't know what your goal looks like. (an unmmerged Excel sheet)
    We can't make suggestions about your goal.
    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,174
    The following macro works on the two examples you've given. You must select the cells you want it to work on before running the macro. I really suspect it won't work on real data for a variety of reasons:
    Sub blah()
    Set zzz = Selection
    zzz.TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True
    Set StartCll = zzz.Cells(1)
    StartCllVal = StartCll.Value
    For Each cll In zzz.Cells
      Set CurrentCell = cll
      With cll
        '    .Select
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        If .Value <> StartCllVal Then
          Application.DisplayAlerts = False: Range(StartCll, .Offset(-1)).MergeCells = True: Application.DisplayAlerts = True
          Set StartCll = cll
          StartCllVal = StartCll.Value
        End If
      End With
    Next cll
    Application.DisplayAlerts = False: Range(StartCll, CurrentCell).MergeCells = True: Application.DisplayAlerts = True
    End Sub
    Supply a file with realistic data for a more refined solution.


    Quote Originally Posted by luxor View Post
    then l can use ms access to create a new excel sheet as unmerged cells.
    ?!!

    Forget how you think you want to get to your goal; what is your goal?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location
    Quote Originally Posted by SamT View Post
    We don't know what your goal looks like. (an unmmerged Excel sheet)
    We can't make suggestions about your goal.
    as u know, working with merged cells in ms excel has some difficulties. after the operation, the sheet can be imported to ms access and then exported as unmerged cells. but this is just a detail, not the main goal.

  7. #7
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location
    thanx for your help, l will try it.

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,174
    Quote Originally Posted by luxor View Post
    the sheet can be imported to ms access and then exported as unmerged cells.
    So what does the file look like after that?!
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,843
    Location
    but this is just a detail, not the main goal.
    Again. We ask, "What is the main goal?"
    Please take the time to read the Forum FAQ

  10. #10
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location
    Hi friends, l am sorry for being late. My aim is just like the operation in the image below. l need a macro to do this in the selected area automatically, otherwise l will have to do it for too much cells manually. l tried the code above but it merged only the numbers, not the cells containing expressions. Thanx again.


    excel vba.jpg

  11. #11
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,174
    Here is some code to try:
    Capture3.jpg
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,843
    Location
    Please take the time to read the Forum FAQ

  13. #13
    VBAX Regular
    Joined
    Feb 2018
    Posts
    6
    Location
    Quote Originally Posted by p45cal View Post
    Here is some code to try:
    Capture3.jpg

    Thanx. l will try this code.

Posting Permissions

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