Results 1 to 5 of 5

Thread: Excel Merge Cells with Macro

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2008
    Posts
    2

    Excel Merge Cells with Macro

    Hi

    I have data in an excel worksheet returned from a dbf database via MSQuery which contains addresses. For example;

    Column A: Company Name
    Column B: Add1
    Column C: Add2
    Column D: Add3
    Column E: Town
    Column F: Zip Code

    I want to combine/merge the cells into a single cell placing a carriage return between each address line and remove any blank cells. For example;

    Company Name
    Add1
    Town
    Zip Code

    Add2, Add3 are removed because they contain no data. In the next record they may contain data in which case they would appear in the merged cell.

    I can merge the cells and insert carriage returns using CHAR(10) but I cannot work out how to remove the blank cells so that the address appears as contiguous in the merged cell. Can anyone help?

    Thanks

  2. #2
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538

    Thumbs up Re: Excel Merge Cells with Macro

    Hi, try this:
    Code:
    Private Sub CommandButton1_Click()
        Dim lastRowIndex As Integer
        Dim lastColumnIndex As Integer
        Dim rowCounterIndex As Integer
        Dim columnCounterIndex As Integer
        Dim currentAddress As String
        
        lastRowIndex = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        lastColumnIndex = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        
        For rowCounterIndex = 1 To lastRowIndex
            currentAddress = ""
            For columnCounterIndex = 1 To lastColumnIndex
                If (Len(CStr(ActiveSheet.Cells(rowCounterIndex, columnCounterIndex))) > 0) Then
                    currentAddress = currentAddress & CStr(ActiveSheet.Cells(rowCounterIndex, columnCounterIndex))
                    currentAddress = currentAddress & Chr(10)
                End If
            Next columnCounterIndex
            ActiveSheet.Cells(rowCounterIndex, CInt(lastColumnIndex + 1)) = currentAddress
        Next rowCounterIndex
    End Sub

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  3. #3
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Excel Merge Cells with Macro

    In cell G2, enter

    =$A2 & CHAR(10) & $B2 & IF($C2="","", CHAR(10) & $C2) & IF($D2="","", CHAR(10) & $D2) & IF($E2="","", CHAR(10) & $E2) & IF($F2="","", CHAR(10) & $F2)

    or remove Add2 and Add3 if blank only:

    =$A2 & CHAR(10) & $B2 & IF($C2="","", CHAR(10) & $C2) & IF($D2="","", CHAR(10) & $D2) & CHAR(10) & $E2 & CHAR(10) & $F2

    then autofill down.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2008
    Posts
    2

    Re: Excel Merge Cells with Macro

    Hi anhn

    Not being that familiar with VB used your formula which works perfectly.

    Thanks really appreciate your help.


  5. #5
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Excel Merge Cells with Macro

    Please use Thread tools on the top to mark your thread as Resolved.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

Posting Permissions

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



Click Here to Expand Forum to Full Width