Moving data from multiple columns

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Gord Dibben]

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