Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining multiple columns into one column - enhancements
Hello everybody,
Today, I've found (on one of the excel newsgroups) a macro "combining multiple columns into one column", posted initially (probably) by Dave Peterson or Bob Phillips. The macro makes the basic work, however I would need some additional enhancements: - my initial page contains formulas, so I would need to convert these data into "paste special/values". - I've noticed that when I put "ctrl + down arrow" on the columns, il goes further than the last cell with value (in fact, it goes always until the row 143), so it should also be resolved somehow... :-), because without this, there will be a lot of "free" space between the "real" end of one column and the start of the next one... - some columns contain blank cells in the middle: what should I add to the code (as an option) if I want to elimitate all the blank cells in the new, combined, column? Many thanks for your help on this... Mark ============================= Sub OneColumn() '''''''''''''''''''''''''''''''''''''''''' 'Macro to copy columns of variable length' 'into 1 continous column in a new sheet ' '''''''''''''''''''''''''''''''''''''''''' 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 idx As Integer Set ws = ActiveWorkbook.ActiveSheet ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column With Sheets.Add .Name = "Alldata" End With idx = Sheets("Alldata").Index Sheets(idx + 1).Activate For colndx = 1 To ilastcol ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _ .End(xlUp).Row Set myRng = Range(Cells(1, colndx), _ Cells(ilastrow, colndx)) With myRng .Copy Sheets("Alldata").Cells(jlastrow + 1, 1) End With Next Sheets("Alldata").Rows("1:1").EntireRow.Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move list of numbers from one column to multiple columns | Excel Discussion (Misc queries) | |||
stacked column chart across multiple columns | Charts and Charting in Excel | |||
Change a Column list into multiple rows & columns | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
merge data from multiple columns to single column | Excel Worksheet Functions |