locked
excel columns handling RRS feed

  • Question

  • User-1314424833 posted

    Hi

    My ques is for excel.

    I have 5 columns each column having thousands of records but of same datatype I want my all records from all 4 columns to be pasted in first column one after another.

    Is it posible without manual copy, paste.

    Thank u

    Saturday, October 24, 2009 6:07 AM

All replies

  • User59160557 posted

    I assume you want to do this in Excel. If so you can use the following macro in VBA:

    Sub test()
    Dim lastRowIndex As Integer
        Dim lastColumnIndex As Integer
        Dim rowCounterIndex As Integer
        Dim columnCounterIndex As Integer
        Dim currentAddress As String
        
        
        lastRowIndex = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        lastColumnIndex = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        currentAddress = CStr(ActiveSheet.Cells(1, 1))
        
        For rowCounterIndex = 1 To lastRowIndex
        currentAddress = CStr(ActiveSheet.Cells(rowCounterIndex, 1))
            For columnCounterIndex = 2 To lastColumnIndex
                If (Len(CStr(ActiveSheet.Cells(rowCounterIndex, columnCounterIndex))) > 0) Then
                    currentAddress = currentAddress & Chr(44) & CStr(ActiveSheet.Cells(rowCounterIndex, columnCounterIndex))
                    ActiveSheet.Cells(rowCounterIndex, columnCounterIndex).Value = ""
                End If
            Next columnCounterIndex
            ActiveSheet.Cells(rowCounterIndex, CInt(1)) = currentAddress
        Next rowCounterIndex
        ActiveSheet.cell
    End Sub
    
    

    The above Macro will merge the content of all the cells with first cell separated by a comma and then clear the content from those merged cells.


    Hope this Helps.

    Saturday, October 24, 2009 4:49 PM