Last post Oct 24, 2009 04:49 PM by codehard
Oct 24, 2009 06:07 AM|tdb|LINK
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.
Oct 24, 2009 04:49 PM|codehard|LINK
I assume you want to do this in Excel. If so you can use the following macro in VBA:
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 = ""
ActiveSheet.Cells(rowCounterIndex, CInt(1)) = currentAddress
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.