Thread: VBA Code For Data Merging from blank cells

1. VBA Code For Data Merging from blank cells

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. 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```

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
•