Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from multiple columns
I am not a programmer so please bear with me as I learn VBA.
I am trying to bring data scattered across 7 columns (b,c,d,e,f,g,h) into column( a). How would I do this. I know that I need to test for the precense of data in the column, select it and move it, then move to the next row. Can I use something like; For i = 1 to 20 ' number of rows Select Case Data Case Column 1 range.offset (0,1)= range.offset(i,1) etc. End Select Next i Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from multiple columns
Hi,
I don't know what kind of data you have in your columns, but I assume there is only data in 1 column. One way to do it would be to concantinate the the data ie. in cell "A2", =B2&C2&D2&E2&F2&G2&H2. This is simple, but it works. You could then get rid of the formula, copy & past special-values. "cdwa" wrote: I am not a programmer so please bear with me as I learn VBA. I am trying to bring data scattered across 7 columns (b,c,d,e,f,g,h) into column( a). How would I do this. I know that I need to test for the precense of data in the column, select it and move it, then move to the next row. Can I use something like; For i = 1 to 20 ' number of rows Select Case Data Case Column 1 range.offset (0,1)= range.offset(i,1) etc. End Select Next i Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from multiple columns
I'm not sure how you have the data 'scattered'. If you have multiple data
bits per row in various B-H columns you should be able to use: 'This will loop through your spreadsheet rows (starting at 1 ending with 20) For intCounterA = 1 To 20 strConcan = "" 'This will loop through each of the B-H columns in the row For intCounterB = 2 To 8 'This combines all the data together in a string strConcan = strConcan & Cells(intCounterA, intCounterB).Value Next 'This sets the first column of the row we're on to the string value cells(intCounterA, 1).value = strConcan Next "cdwa" wrote: I am not a programmer so please bear with me as I learn VBA. I am trying to bring data scattered across 7 columns (b,c,d,e,f,g,h) into column( a). How would I do this. I know that I need to test for the precense of data in the column, select it and move it, then move to the next row. Can I use something like; For i = 1 to 20 ' number of rows Select Case Data Case Column 1 range.offset (0,1)= range.offset(i,1) etc. End Select Next i Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving data from multiple columns
In what order do you the data to appear?
One long column with 140 rows? Row1 to A1:A7 then row2 to A8:A14 Sub c() Dim rngIn As Range, rngOut As Range, i As Long Set rngIn = Range("A1:G20") Set rngOut = Range("H1") For i = 1 To rngIn.Rows.Count rngIn.Rows(i).Copy rngOut(1 + (rngIn.Columns.Count) * (i - 1)).PasteSpecial _ Transpose:=True Next Application.CutCopyMode = False Columns("A:G").EntireColumn.Delete Range("A1").Select End Sub Gord Dibben Excel MVP On Tue, 30 Nov 2004 09:19:04 -0800, "cdwa" wrote: I am not a programmer so please bear with me as I learn VBA. I am trying to bring data scattered across 7 columns (b,c,d,e,f,g,h) into column( a). How would I do this. I know that I need to test for the precense of data in the column, select it and move it, then move to the next row. Can I use something like; For i = 1 to 20 ' number of rows Select Case Data Case Column 1 range.offset (0,1)= range.offset(i,1) etc. End Select Next i Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving multiple data in a cell to other columns | Excel Worksheet Functions | |||
Moving data from row to columns | New Users to Excel | |||
Moving data between columns | Excel Discussion (Misc queries) | |||
How do I stop the TAB from moving over multiple columns | Excel Discussion (Misc queries) | |||
IF statements and moving data to different columns | Excel Worksheet Functions |