Move data in alternate rows to the next column without loosing the hyperlink function in the initial column

Hi Arsil,

The problem with the previously suggested solution seems to have been that it didn't copy the hyperlinks as hyperlinks. I might have preferred to deal with that but it will take too much time to find out what is needed. Therefore I decided to put the whole thing into VBA the way you request. Please run this code from a standard code module.

Sub MergeAlternativeRows()
    ' 24 Jul 2019

    Dim Rl As Long
    Dim Rt() As Long, i As Long
    Dim R As Long
    Dim C As Long
    Dim Tmp As Variant, Rmax As Long

    With ThisWorkbook.Worksheets("Sheet2")              ' change as appropriate
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        ReDim Rt(Rl)
        For R = 1 To Rl
            ' pick rows with hyperlinks in column A
            If .Cells(R, "A").Hyperlinks.Count Then
                Rt(i) = R
                i = i + 1
            End If
        Next R
        ReDim Preserve Rt(i - 1)

        Application.ScreenUpdating = False
        For i = UBound(Rt) To 0 Step -1
            C = 2                                       ' specified B as target column
            R = Rt(i) + 1
            If i < UBound(Rt) Then
                Rmax = Rt(i + 1) - 1
            Else
                Rmax = Rl
            End If

            Do While R <= Rmax
                Tmp = Trim(.Cells(R, "A").Value)
                If Len(Tmp) Then
                    .Cells(Rt(i), C).Value = Tmp
                    C = C + 1
                End If
                R = R + 1
            Loop

            ' delete non-hyperlink rows
            For R = Rmax To (Rt(i) + 1) Step -1
                .Rows(R).EntireRow.Delete
            Next R
        Next i
        Application.ScreenUpdating = True
    End With
End Sub

The code is a little more complicated than it probably needed to be because I mistook some of the rows in your original data as being blank. As the code is now it would ignore blank rows completely but if there is more than one row between hyperlinks the non-blank cells from column A would be copied to columns B, C etc. So, just make sure that they don't over-write anything in those columns.

I hope this solves your problem. Have a good day!