Macro to cut the 3 rows text in to a single row

**rajkumarmp** · 05-20-2013, 02:54 PM

Hi Champs,

I have attached a file which has input and output tab.
The Input tab has data where a text of a specific row is split-ted across 2 or more than 2 rows.

I need a macro for data to be exact as in Output tab. Thanks in Advance.

**tehneXus** · 05-20-2013, 03:30 PM

Hi,

try: text2.xls

contains:

Option Explicit

Private Sub cmdConvert_Click()
Selection.Delete Shift:=xlToLeft
    Dim i As Long, lngMaxRows As Long
    Application.ScreenUpdating = False
    
    lngMaxRows = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lngMaxRows
        If Not IsEmpty(Cells(i, 6)) Then
            MergeCells i, 2
        ElseIf Not IsEmpty(Cells(i, 5)) Then
            MergeCells i, 1
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

Private Sub MergeCells(lngRow As Long, rpt As Integer)
    Dim i As Integer
    For i = 1 To rpt
        Cells(lngRow, 2).Value = Cells(lngRow, 2).Value & Cells(lngRow, 3).Value
        Cells(lngRow, 3).Delete Shift:=xlToLeft
    Next i
End Sub