Thread: Populating one column with row data

I have a tough problem that I can't seem to figure out the formula for.
I am trying to populate a row of columns into one column in another sheet. The columns are populated with words. I'm trying to get the formula to read data in the first column, then when there is no data left, jump to the top of the next column. Then I'm trying to compile all of the data in the one column on a seperate sheet.
I am totally stumped! any help would be great. thanks

Originally Posted by bton24

I have a tough problem that I can't seem to figure out the formula for.
I am trying to populate a row of columns into one column in another sheet. The columns are populated with words. I'm trying to get the formula to read data in the first column, then when there is no data left, jump to the top of the next column. Then I'm trying to compile all of the data in the one column on a seperate sheet.
I am totally stumped! any help would be great. thanks

would you please give some detail of your data with cell references where data resides.

Regards.

By row of columns, I assume you mean there are a number of columns next to each other.

This custom macro will do it for you. just click in the source range and then run the macro

Sub SingleColumn()
    Dim myRange As Range
    Dim newBook As Workbook
    Dim ThisBook As Workbook
    Dim myCols As Integer
    Dim myRows As Integer
    Dim C As Integer
    Dim R As Integer
    
    Set myRange = Selection.CurrentRegion
    myCols = Selection.CurrentRegion.Columns.Count
    myRows = Selection.CurrentRegion.Rows.Count
    Set ThisBook = ActiveWorkbook
    Workbooks.Add
    Set newBook = ActiveWorkbook
    ThisBook.Activate
    For C = 1 To myCols
        myRange.Range(Cells(1, C), Cells(myRows, C)).Copy
        newBook.Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.End(xlDown).Offset(1, 0).Select
        ThisBook.Activate
    Next C
End Sub