Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    I would like to have a macro select a group of cells to the right of an active cell and then merge those cells. Problem is, the active cell could be any one of about 20 different cells in the same column so I can't specify it in the macro.

    For example, my active cell could be A1, A2, A3, A4 etc. For whichever one of these happens to be active I would able to be able to run a macro to merge it with 4 or 5 cells to the right.

    This would be an easy task for me if the active cell were always the same (say always A1), but it's not so I'm not sure how to pull this off.

    Can anyone send me in the right direction to do this?

    Thanks for your help.
    BH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following macro will resize the four cells to the right of the active cell, wherever the active cell is:

    Code:
    Sub MergeRight()
      ActiveCell.Offset(0, 1).Resize(1, 4).MergeCells = True
    End Sub
    If you want to merge 5 cells, change Resize(1, 4) to Resize(1, 5)

    PS
    Personally, I don't like to merge cells, it causes problems when you copy/paste cells. I prefer to use the "Center across selection" alignment. The following macro will apply this to the four cells to the right of the active cell:

    Code:
    Sub CenterAcross4()
      ActiveCell.Offset(0, 1).Resize(1, 4).HorizontalAlignment = xlCenterAcrossSelection
    End Sub
    It's up to you to decide which to use, of course.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following will merge the current cell with the 4 cells to its right. Change the 4 in the offset to the number of columns you want to include in the merge.

    Code:
    Sub MakeMerge()
    	 With Range(Selection, Selection.Offset(0, 4))
    		   .MergeCells = True
    	End With
    End Sub
    or a shortened version:

    Code:
    Sub MakeMerge()
    Range(Selection, Selection.Offset(0, 3)).MergeCells = True
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks guys. I'll work on it using one method or the other.

    Much appreciated.

    BH

Posting Permissions

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