concatenate columns

i need to automate the concatenation of columns

please see attached. anywhere there is text in col A , that is not a URL, i need to create a new value that is concatenating the columns to the right. note, there is not a fixed number of columns to the right.

then, i need all rows below rows with text to be deleted (not ones below urls rows). so delete row 5 but not row 7.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
The following code will concatenate all column-delimited strings and remove blank rows except those after a URL (i.e., string begins with "[url")

Sub Concat_Columns()
    Dim rng As Range
    Dim cl As Object
    Dim intLastCol As Integer
    Dim c As Integer
    'Note: beginning processing at row 4 to avoid header rows
    'Concatenate strings across multiple columns
    intLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
    For c = 2 To intLastCol
        Range("A4:A" & Cells.SpecialCells(xlCellTypeLastCell).Row).Select
        Set rng = Selection
        For Each cl In rng
            If cl.Offset(0, 1).Value <> "" Then
                cl.Value = cl.Value & cl.Offset(0, 1).Value
            End If
        Next cl
    Next c
    'Remove extra blank rows after body text; selection still active
    For Each cl In rng
        If cl.Value = "" And Left(cl.Offset(-1, 0).Value, 4) <> "[url" Then
        End If
    Next cl
End Sub

Open in new window

Some notes:
1) Your original CSV file is broken up into a header (two rows), article titles, body text, and URLs.  The end of the second row of data contains the title of the first article and is not assigned its own row as the rest are.
2) The above code removes all blank rows excepts those below the URL.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
finnstoneAuthor Commented:
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.