1
Thanks

A few words of thanks would be greatly appreciated.

Excel - Macro to format report

Issue

Hopefully I can word what I am looking for in a way that makes sense to everyone. I have a customer report that I run and it exports in an ugly way.

It looks like this:

Address 
Suburb State Postcode 
Delivery Instructions 
Phone Number 
Fax Number 
Email Address 

All the above information is in column A, and I would like to create a macro that has the information in column A-F so each heading has a new column.
I can record really basic macros, so I will probably need someone to write one for me so I can simply copy and paste *sorry*.

Hopefully this is really simple to someone and they are willing to help me out. Any suggestions/advice is greatly appreciated!

Solution

Objective:
To splits lines is a cell and move each line to a new cell on the same row.

Assumptions:
1. Data start at ROW 2
2. The data is in column A
3. Column next to A is where the data needs to be populated
4. Each line in the data cells should be moved to a new cell in the same row

Steps:
1. Read the assumptions
2. Make a backup of the file
3. Press ALT + F11 and insert a new module
4. Paste the code (after steps)
5. Execute the code

Code:

Sub SpiltData()

Dim lMaxRows As Long
Dim lRowBeanCounter As Long
Dim vPos As Variant
Dim sHold As String
Dim sTemp As String
Dim iCellCounter As Integer
Dim lStartAtRow As Long
    
    lStartAtRow = 1
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lRowBeanCounter = lStartAtRow To lMaxRows
    
        sTemp = Cells(lRowBeanCounter, "A")
        iCellCounter = 2
        
        Do While sTemp <> ""
            vPos = 0
            vPos = InStr(1, sTemp, Chr(10))
            
            If vPos > 0 Then
                sHold = Left(sTemp, vPos - 1)
                sTemp = Trim(Mid(sTemp, vPos + 1))
            Else
                sHold = sTemp
                sTemp = ""
            End If
            
            iCellCounter = iCellCounter + 1
            Cells(lRowBeanCounter, iCellCounter) = sHold
        Loop
        
    
    Next lRowBeanCounter
End Sub

Note

Thanks to rizvisa1 for this tip on the forum.

1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Macro to format report », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).