VBA Code For Data Merging from blank cells

Abhishek Ghai

New member
Joined
Jul 7, 2012
Messages
5
Reaction score
0
Points
0
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​
 
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:
Hi Bob,

You are a true genius.....:clap2: 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....
:bounce:
Cheers
Abhishek
 
Back
Top