Hi, I saw your code, it is great, also I modified it to select where to
put the single column and frome where to take the values, all this just
wiht input boxes.
By question is, which is the correct syntaxis for the setting an
instruction that clears the original range where I copied the data?
The syntax I'm using to clear the range is
[Range("TABLE_START_ROW", "TABLE_END_COLUMN").Clear]
but obviously it is wrong.
Hope you can help me, thanks a lot.
Chip Pearson wrote:
Re: Moving data from many columns to a single column
14-nov-08
Daniel,
There are any number of ways to do this. Here are two: one using
worksheet formulas and one using VBA code.
FORMULA:
Suppose your data table begins in cell B7 and each column has (at
most) 50 rows. Also, assume you want your single column list to begin
at cell R24. Enter the following formula in R24 and copy down to about
row 2000 (or far enough so that all values in the data table are
accounted for):
=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)
This will create a single column that contains all the data within the
data table. However, it assumes that all columns have the same number
of elements, so two things happen: blanks will be present in the
single column list where there are blanks in the data table, and any
column with more that 50 rows will be truncated in the column list.
VBA CODE:
If you prefer a VBA approach, use code like the following. Change the
Const values to meet your needs.
Sub AAA()
Dim Dest As Range
Dim R As Range
Dim WS As Worksheet
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' change constants to fit your need
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Const LIST_START_ROW = 21 ' result list begins in this row
Const LIST_START_COLUMN = "D" ' result list begins in this
column
Const TABLE_START_ROW = 7 ' data table starts in this row
Const TABLE_START_COLUMN = "B" ' data table starts in this column
Const TABLE_END_COLUMN = "J" ' data table ends in this column
Const SHEET_NAME = "Sheet1" ' result list and data table
reside on this table
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set WS = Worksheets(SHEET_NAME)
Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)
Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)
Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column
Dest.Value = R.Value
Set R = R(2, 1)
If R.Value = vbNullString Then
Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)
End If
Set Dest = Dest(2, 1)
Loop
End Sub
This code assumes that while the coluimns may have varying length, the
presence of a blank cell marks the end of each column.
Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Previous Posts In This Thread:
On viernes, 14 de noviembre de 2008 01:01 p.m.
Daniel wrote:
Moving data from many columns to a single column
Hi
I have Excel 2007, I have a spreadsheet with 38 columns with product names
in an average of
50 rows in each column, one name per cell.
I need to put all of these products into one column.
How can I do that automatically.
thanks
Daniel
On viernes, 14 de noviembre de 2008 02:03 p.m.
Daniel.C wrote:
Re: Moving data from many columns to a single column
Hi.
Give a try to :
Sub test()
Dim c As Range, Sh As Worksheet
Set Sh = ActiveSheet
Sheets.Add before:=Sheets(1)
With Sh
For i = 1 To 38
For Each c In Range(.Cells(1, i), .Cells(65536, i).End(xlUp))
Range("A65536").End(xlUp).Offset(1) = c
Next c
Next i
End With
End Sub
HTH
Daniel
On viernes, 14 de noviembre de 2008 02:14 p.m.
Daniel wrote:
Re: Moving data from many columns to a single column
Thanks for your help, it is a bit (a lot) over my head but I will
try to work it out and hopefully learn something in the process.
regards
Daniel
On viernes, 14 de noviembre de 2008 03:34 p.m.
Chip Pearson wrote:
Re: Moving data from many columns to a single column
Daniel,
There are any number of ways to do this. Here are two: one using
worksheet formulas and one using VBA code.
FORMULA:
Suppose your data table begins in cell B7 and each column has (at
most) 50 rows. Also, assume you want your single column list to begin
at cell R24. Enter the following formula in R24 and copy down to about
row 2000 (or far enough so that all values in the data table are
accounted for):
=OFFSET($B$7,MOD(ROW()-ROW($R$24),50),TRUNC((ROW()-ROW($R$24))/50),1,1)
This will create a single column that contains all the data within the
data table. However, it assumes that all columns have the same number
of elements, so two things happen: blanks will be present in the
single column list where there are blanks in the data table, and any
column with more that 50 rows will be truncated in the column list.
VBA CODE:
If you prefer a VBA approach, use code like the following. Change the
Const values to meet your needs.
Sub AAA()
Dim Dest As Range
Dim R As Range
Dim WS As Worksheet
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' change constants to fit your need
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Const LIST_START_ROW = 21 ' result list begins in this row
Const LIST_START_COLUMN = "D" ' result list begins in this
column
Const TABLE_START_ROW = 7 ' data table starts in this row
Const TABLE_START_COLUMN = "B" ' data table starts in this column
Const TABLE_END_COLUMN = "J" ' data table ends in this column
Const SHEET_NAME = "Sheet1" ' result list and data table
reside on this table
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set WS = Worksheets(SHEET_NAME)
Set Dest = WS.Cells(LIST_START_ROW, LIST_START_COLUMN)
Set R = WS.Cells(TABLE_START_ROW, TABLE_START_COLUMN)
Do Until R.Column > Cells(1, TABLE_END_COLUMN).Column
Dest.Value = R.Value
Set R = R(2, 1)
If R.Value = vbNullString Then
Set R = R(1, 2).EntireColumn.Cells(TABLE_START_ROW, 1)
End If
Set Dest = Dest(2, 1)
Loop
End Sub
This code assumes that while the coluimns may have varying length, the
presence of a blank cell marks the end of each column.
Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On viernes, 14 de noviembre de 2008 09:39 p.m.
Daniel wrote:
Re: Moving data from many columns to a single column
Thanks Chip, appreciate your advice, and it worked well.
regards
Daniel
On s?bado, 15 de noviembre de 2008 12:35 a.m.
ShaneDevenshir wrote:
RE: Moving data from many columns to a single column
Hi,
A slightly different approach, if order doesn't matter, which assumes that
the most items in any column will be 59, change this to any number you
want,
it assumes that the range C1:AN59 encompasses all the data, blank or not.
Enter this in cell A1 and copy it down.
=INDEX($C$1:$AN$59,MOD(ROW(A1),59),ROUNDUP(ROW(A1)/59,0))
Select all these formulas and choose Copy, then choose Edit, Paste
Special,
Values. Sort the results Descending. All the zeros will be at the bottom
of
the list.
Cheers,
Shane Devenshire
:
On s?bado, 15 de noviembre de 2008 07:47 p.m.
Daniel wrote:
Re: Moving data from many columns to a single column
great, also worked well and was probably the simplest,
thanks to all who gave advice.
I have learned a great deal.
regards
Daniel
message
EggHeadCafe - Software Developer Portal of Choice
Working with Client Side Xml Data Islands from Server-Side ASP.NET code
http://www.eggheadcafe.com/tutorial...a9-e41f967e573b/working-with-client-side.aspx
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.