[RESOLVED] Merge blank cells Macro in Excel-VBForums
Results 1 to 10 of 10

Thread: [RESOLVED] Merge blank cells Macro in Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Resolved [RESOLVED] Merge blank cells Macro in Excel

    I am wondering if there is a way to write a macro in Excel to where it searches from say cell A13 to C2000 and merges all cells which are blank with the cells to the left of them.

    Any and all help is welcome!

    Thanks!

  2. #2
    Addicted Member
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    242

    Re: Merge blank cells Macro in Excel

    Check out the Range.Merge method. It merges the specified ranges into 1 cell.
    e.g.
    Code:
    Range("a1:B2").Merge ' merge all the cells in the range into 1 cell
    Range("a1:B2").Merge True 'the optional parameter merges cells of each row separately.
    -EM
    ---
    REMEMBER: If your issue is resolved, use the Thread Tools menu to set it as such, and be sure to rate the posts that help you the most!


    Just because I was jealous of g4hsean!

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Re: Merge blank cells Macro in Excel

    I am guessing that this will merge the cells whether there is data in them or not. That is a good start although I don't know what the ranges are going to be. I will have lots of data throughout the range of A13 to C2000 and will only want to merge the cells that are blank with the cells that are to the left of them. So maybe if there is a way to search for the ranges of blank cells and then use the Range("a1:B2").Merge True.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,928

    Re: Merge blank cells Macro in Excel

    column A has no cells to the left

    vb Code:
    1. for each mycell in range("B13:c2000")
    2.    if isempty(mycell) then mycell.offset(,-1).resize(,2).merge
    3. next
    not tested and does not check if the cell to the left of the empty cell is also empty
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2010
    Posts
    3

    Re: Merge blank cells Macro in Excel

    This is perfect! This is exactly what I was looking for! Thank you!

    It actually ended up being the following but even I could figure out that little change.

    for each mycell in range("B13:c2000")
    if isempty(mycell) then mycell.offset(,0).resize(,2).merge
    next

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,928

    Re: [RESOLVED] Merge blank cells Macro in Excel

    so you merge with the cell to the right?
    in that case you may need to use range("a13:c2000")
    you can omit the offset altogether
    if isempty(mycell) then mycell.resize(,2).merge
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    New Member
    Join Date
    Feb 2010
    Posts
    1

    Re: [RESOLVED] Merge blank cells Macro in Excel

    Is there a way to fill cells to the right of the column with data from the left and not merge them?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,928

    Re: [RESOLVED] Merge blank cells Macro in Excel

    if isempty(mycell) then mycell = mycell.offset(,-1)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    New Member
    Join Date
    Nov 2010
    Posts
    1

    Re: [RESOLVED] Merge blank cells Macro in Excel

    Can you please help me with same sort of issue.
    Where I have to merge the blank cells below with the cell above having the data.
    For eg....
    Data
    Blank
    Blank
    Blank

    all the 4 cells should be merged
    and data should be taken from the 1st cell

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,928

    Re: [RESOLVED] Merge blank cells Macro in Excel

    try like
    vb Code:
    1. For i = 2 To Cells(65535, 1).End(xlUp).Row
    2.     If IsEmpty(Cells(i, 1)) Then Range(Cells(i - 1, 1), Cells(i, 1)).Merge
    3. Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.