concatenate columns

finnstone
finnstone used Ask the Experts™
on
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.
pehub-2019-03-07--1-.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
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
        Range("B:B").EntireColumn.Delete
    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
            cl.EntireRow.Delete
        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.

Regards,
-Glenn

Author

Commented:
perfect!

Author

Commented:
thx
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You're welcome.
-Glenn

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial