Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge columns
I have 10+ columns of data. I need to have all columns merged one under
another into one column.For example column A. I can do it manually by cutting the data in each column and pasting it below the last record in column A. But there should be a way to automate that.Any help with the code will be apprciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge columns
Try something like the following, where
C_COLUMNS_TO_MERGE is the number of columns to merge C_START_COLUMN is the first column of data to merge C_START_ROW is the row to start on. The procedure will start with row C_START_ROW and continue downwards until an empty cell is encountered in C_START_COLUMN. Sub MergeThem() Const C_COLUMNS_TO_MERGE = 3 Const C_START_COLUMN = 1 Const C_START_ROW = 2 Dim S As String Dim Ndx As Long Dim Rng As Range Set Rng = ActiveSheet.Cells(C_START_ROW, C_START_COLUMN) Application.DisplayAlerts = False Do Until Rng.Value = vbNullString S = vbNullString For Ndx = 1 To C_COLUMNS_TO_MERGE S = S & Rng(1, Ndx).Text & IIf(Ndx < C_COLUMNS_TO_MERGE, " ", "") Next Ndx Rng.Resize(1, C_COLUMNS_TO_MERGE).Merge Rng.Value = S Set Rng = Rng(2, 1) Loop Application.DisplayAlerts = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "vlad" wrote in message ... I have 10+ columns of data. I need to have all columns merged one under another into one column.For example column A. I can do it manually by cutting the data in each column and pasting it below the last record in column A. But there should be a way to automate that.Any help with the code will be apprciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge columns
vlad
Try this from Bernie Dietrick Sub OneColumnV2() '''''''''''''''''''''''''''''''''''''''''' 'Macro to copy columns of variable length' 'into 1 continous column in a new sheet ' 'Modified 17 FEb 2006 by BD '''''''''''''''''''''''''''''''''''''''''' Dim iLastcol As Long Dim iLastRow As Long Dim jLastrow As Long Dim ColNdx As Long Dim WS As Worksheet Dim myRng As Range Dim ExcludeBlanks As Boolean Dim myCell As Range ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes) Set WS = ActiveSheet iLastcol = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column On Error Resume Next Application.DisplayAlerts = False Worksheets("Alldata").Delete Application.DisplayAlerts = True Sheets.Add.Name = "Alldata" For ColNdx = 1 To iLastcol iLastRow = WS.Cells(WS.Rows.Count, ColNdx).End(xlUp).Row Set myRng = WS.Range(WS.Cells(1, ColNdx), _ WS.Cells(iLastRow, ColNdx)) If ExcludeBlanks Then For Each myCell In myRng If myCell.Value < "" Then jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _ .End(xlUp).Row myCell.Copy Sheets("Alldata").Cells(jLastrow + 1, 1) _ .PasteSpecial xlPasteValues End If Next myCell Else myRng.Copy jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _ .End(xlUp).Row myCell.Copy Sheets("Alldata").Cells(jLastrow + 1, 1) _ .PasteSpecial xlPasteValues End If Next Sheets("Alldata").Rows("1:1").EntireRow.Delete WS.Activate End Sub Gord Dibben MS Excel MVP On Tue, 6 Mar 2007 16:16:03 -0800, vlad wrote: I have 10+ columns of data. I need to have all columns merged one under another into one column.For example column A. I can do it manually by cutting the data in each column and pasting it below the last record in column A. But there should be a way to automate that.Any help with the code will be apprciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
merge columns
Hello,
This might do what you're after - but make sure the data starts in cell A1. Dan. Option Explicit Sub ToOneColumn() Dim cntI As Integer Dim cntJ As Integer Dim TotalRows As Integer Dim TotalCols As Integer TotalRows = ActiveSheet.UsedRange.Rows.Count TotalCols = ActiveSheet.UsedRange.Columns.Count For cntJ = 2 To TotalCols Cells(1, cntJ).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut Cells((cntJ - 1) * TotalRows + 1, 1).Select ActiveSheet.Paste Next cntJ Cells(1, 1).Select End Sub "vlad" wrote: I have 10+ columns of data. I need to have all columns merged one under another into one column.For example column A. I can do it manually by cutting the data in each column and pasting it below the last record in column A. But there should be a way to automate that.Any help with the code will be apprciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge Columns | Excel Discussion (Misc queries) | |||
Merge Columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
How to merge columns from excel and how to set customize each columns | Excel Programming | |||
I want to merge two columns | New Users to Excel |