Results 1 to 3 of 3

Thread: VBA Code For Data Merging from blank cells

  1. #1

    VBA Code For Data Merging from blank cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, Please help me with a VBA code for data merging of two cells in a row, where when ever a blank appears under A coloumn of cell1, all the cells of that row (eg from A1 till J1) data should merge and if there is no blank under a row in A coloumn, there should be no merging of data.Here challenge is the blank is not specified sometimes there is a blank of (2, 3, 4 , 5 , 6, 7, 8, 9, 10, 11, 12, 13 & 14) Coloumn A rows.

    Problem:-

    A1 A2 A3 A4 A5
    B2 B4
    C3 C5
    D1 D2 D3 D4 D5
    E3 E5
    F1 F2 F3 F4 F5
    G2 G3
    H1 H2 H3 H4 H5
    I1 I2 I3 I4 I5
    J1 J2 J3 J4 J5
    K2 K3
    (please consider numbers the same under each alphabet like B2 should be under A2 so that you can find a blank under the same alphabet row)


    Solution should be
    A1 A2/B2 A3/C3 A4/B4 A5/C5
    D1 D2 D3/E3 D4 D5/E5
    F1 F2/G2 F3/G3 F4 F5
    H1 H2 H3 H4 H4 H5
    I1 I2 I3 I4 I5
    J1 J2/K2 J3/K3 J4 J5

  2. #2
    Code:
    Public Sub ProcessData()
    Dim Lastrow As Long
    Dim Markrow As Long
    Dim i As Long, j As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            Lastrow = .UsedRange.Rows.Count
            For i = Lastrow To 2 Step -1
            
                If .Cells(i, "A").Value2 = "" Then
                
                    For j = 1 To 5
                    
                        If .Cells(i, j).Value2 <> "" Then
                        
                            If .Cells(i - 1, j).Value2 <> "" Then .Cells(i - 1, j).Value2 = .Cells(i - 1, j).Value2 & "/"
                        
                            .Cells(i - 1, j).Value2 = .Cells(i - 1, j).Value2 & .Cells(i, j).Value2
                        End If
                    Next j
                    
                    .Rows(i).Delete
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub
    Last edited by Bob Phillips; 2012-07-22 at 10:08 PM.

  3. #3
    Hi Bob,

    You are a true genius..... The above coding worked fine for me, I just made a small change, instead of a slash after data merge I added space so that sentence should look in continuation, thanks for your great help....

    Cheers
    Abhishek

Posting Permissions

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