Merge data from multiple columns onto 1 column

[anglais428]

Hi guys,

I'm assuming this will be a macro but I have a spreadsheet with data starting in cell C1 and ending in BV290. In column C I have information that ranges from C1:C9, in Column D D1:D5, and so on until Column BV. What I would like to do is have all the data displayed in one column (Column A). So Column A would have (in A1) the value in C1, in A2 the value of C2...., in A10 the value of D1 etc.

Any help would be great,

Thanks,

 

[gsbelbin]

You could try this VBA

    Dim lintRow As Long
    Dim i As Integer
    Dim j As Integer
 
    lintRow = 1
    i = 1
    j = 3
 
    With Worksheets("Sheet1")
        Do Until Cells(i, j).Value = ""
            Do Until Cells(i, j).Value = ""
                Cells(lintRow, 1).Value = Cells(i, j).Value
                lintRow = lintRow + 1
                i = i + 1
            Loop
            j = j + 1
            i = 1
        Loop
    End With

 

[MickG]

Hi, Try :-

[COLOR="Navy"]Sub[/COLOR] MG05Aug04
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range("C1:BV290")
c = 1
[COLOR="Navy"]For[/COLOR] Ac = 1 To 72
    Range("A" & c).Resize(290) = Application.Index(rng.Value, , Ac)
    c = c + 290
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
rng.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[anglais428]

Thanks to you both, working great!