Main characters to merge with Sub-characters in record

[gentle2005]

Hi,

I have sample file attached for the merger of main test characters with the sub-characters. I have 10k number of rows wherein different main characters to merge with sub-characters nearly 3k rows. could somebody help me with VBA code to make life easier? comment the code to understand VBA logic. Thank you.

[Paul_Hossler]

This is marked [Solved] -- did you figure it out?

[gentle2005]

No. I am surprised to see it as "solved". Problem remain standstill.

[Paul_Hossler]

OK, this macro runs on a sheet called 'Data' a copy of your input - change the name if you want

Option Explicit

Sub MergeCharacteristics()
    Dim iLastRow As Long, iRowWithData As Long, iRow As Long
    Dim wsData As Worksheet
    Dim rBlanks As Range, rCell As Range
    
    
    'faster
    Application.ScreenUpdating = False
    
    'save the data sheet in variable
    Set wsData = Worksheets("Data")

    'using  'With' assume that all <dot>somethings refer to wsData - clearer and saves typing.
    'some people think it's faster also
    With wsData

        'from last row in the WS, col 2 go up to find first non-empty cell
        iLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

        'hold just the empty cells in col A = 1 in a Range variable (have to use 'Set' for objects)
        Set rBlanks = .Range(.Cells(1, 1), .Cells(iLastRow, 1)).SpecialCells(xlCellTypeBlanks)

        'go through each of the empty cells in rBlanks
        For Each rCell In rBlanks.Cells

            'if the cell one col over is not blank
            If Len(rCell.Offset(0, 1).Value) > 0 Then
                    
                'go up and find a non-empty cell up from the blank one
                iRowWithData = rCell.End(xlUp).Row
        
                'make the cell to the left of the empty one = the data from the non-empty cell above + the original data
                'note the dot on Cells to use wsData
                rCell.Offset(0, 1).Value = .Cells(iRowWithData, 2).Value & " " & rCell.Offset(0, 1).Value

            End If

        Next

        'cleanup the second col
        For iRow = 2 To iLastRow
            'worksheet Trim() acts differently than VBA Trim
            .Cells(iRow, 2).Value = Application.WorksheetFunction.Trim(.Cells(iRow, 2).Value)
        Next iRow

    End With

    Application.ScreenUpdating = True

End Sub

[gentle2005]

Thank you. Mission accomplished!!! but need some changes like the file attached. I want to learn VBA becoz it is thrilling me always; just 8 lines of code make millions of rows obey. As a VBA expert, guide me where and how to start excel VBA. Thank you.