Moving data into separate rows

[Bensmeat]

Hi

I have a question its quite hard to explain, ive got a spreadsheet with information that has been input on separate lines within a single row using the alt+enter function.

I need to create a macro that takes the information out of the row and places it in separate rows i.e each line within the row needs to go on its own row, i think this needs a picture to explain..

Thanks alot for your help!

 

[Trebor76]

Hi there,

Welcome to the forum.

Run the following AltEnterExtract macro on the first cell you want the data to be extracted to (it will work in any column but where the source data resides - i.e. Column A in this case):

Sub AltEnterExtract()

Dim intChrCnt As Integer
Dim varTemp As Variant

    'Assumes the 'ALT+ENTER' data is in A1 to A[whatever row]. _
    Change if required.
    For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    
        For intChrCnt = 1 To Len(cell.Value)
            If Mid$(cell.Value, intChrCnt, 1) <> Chr(10) Then
                varTemp = varTemp & Mid$(cell.Value, intChrCnt, 1)
            Else
                'Output will be in the Column the cursor was on _
                (activecell) when the macro was executed.
                With ActiveCell
                    .Value = varTemp
                    .Offset(1, 0).Select
                End With
                varTemp = ""
            End If
        Next
        
        If varTemp <> "" Then
            With ActiveCell
                .Value = varTemp
                .Offset(1, 0).Select
            End With
            varTemp = ""
        End If
        
    Next cell
    
End Sub

HTH

Robert

 

[Bensmeat]

Works perfectly!

Thankyou very much

 

[Trebor76]

You're welcome :wink: