Delete empty cells and merge columns
I have a rather large worksheet with over 50 columns. I need these columns combined into one column, and manual cut and paste would take too long. And there are empty cells, I also need them to be removed. Finally all the columns needs to be merged into A. Any ideas? Thanks
file.xls
file.xls
ASKER
have you tried it on my attached file?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, works great and very nice article
you could actually create an Excel formula to create this for you, but if it's a 1-time deal, maybe some VBA would be better.
Sub MergeColumns()
For iRow=1 to iLastRow
strMerge = ""
For iColumn=1 to 50
if Cells(iRow, iColumn).Value <> "" then
strMerge = strMerge & "," & Cells(iRow, iColumn).Value
End If
Cells(iRow, 51).Value = mid(strMerge,2)
Next iColumn
Next iRow
' optionally add extra code to delete columns 1-50
End Sub