Copying All the Cell Contents to "A" Column

[madhuchelliah]

Hello Guys, I have data in A column and scattered around randomly from B column to Z column. I want copy those data to A column one after one in A column. Please heads up. Thank you.

 

[Rick Rothstein]

Z

Hello Guys, I have data in A column and scattered around randomly from B column to Z column. I want copy those data to A column one after one in A column. Please heads up. Thank you.

If this was your data, how should the list in Column A look afterwards...

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]One[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]Two[/TD]
[TD]BBB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Three[/TD]
[TD]CCC[/TD]
[TD]DDD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Four[/TD]
[TD][/TD]
[TD][/TD]
[TD]EEE[/TD]
[/TR]
</tbody>[/TABLE]

 

[madhuchelliah]

Hello Rick, Thanks for the response. I just want all the contents in the A column. it doesn't matter how the order is in A column. If it is easy to copy each column data one by one then see the result in the table below.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]One
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Two
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Three
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Four
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EEE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hello Rick, Thanks for the response. I just want all the contents in the A column. it doesn't matter how the order is in A column. If it is easy to copy each column data one by one then see the result in the table below.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]One
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Two
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Three
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Four
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EEE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub AllTextToColumnA()
  Dim LastCol As Long, Combined As String, Rng As Range
  Application.ScreenUpdating = False
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  For Each Rng In ActiveSheet.UsedRange.Columns
    Rng.Cut Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Next
  Columns("A").SpecialCells(xlBlanks).Delete xlShiftUp
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[madhuchelliah]

Thank you Rick. Working Great !!!!!!!!!!!!!

 

[madhuchelliah]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub AllTextToColumnA()
  Dim LastCol As Long, Combined As String, Rng As Range
  Application.ScreenUpdating = False
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  For Each Rng In ActiveSheet.UsedRange.Columns
    Rng.Cut Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Next
  Columns("A").SpecialCells(xlBlanks).Delete xlShiftUp
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hello Rick, I am getting the following error while running the code "The selection is not valid. make sure the copy and paste areas don't overlap unless they are the same size and shape". I can't figure out the issue. Could you please assist me to get rid of this?

 

[Rick Rothstein]

Hello Rick, I am getting the following error while running the code "The selection is not valid. make sure the copy and paste areas don't overlap unless they are the same size and shape". I can't figure out the issue. Could you please assist me to get rid of this?

Can you post a copy of the workbook (with the original data before running my code against it) to DropBox so that I can download it and follow how my code interacts with it while watching it execute?

 

[madhuchelliah]

Hello Rick, I guess the data is not an issue. After getting error replace that sheet with new sheet, then the code runs perfectly. After some time it starts showing the same error. I found some dissimilarity during successful and unsuccessful attempts. During successful attempt i can select the entire column by clicking the heading but during unsuccessful attempt when error shows i cant able to select the entire column. Clicking on the heading doing nothing. Since the error also stating that invalid selection the problem might be with the selection. BTW i cant able to share data its confidential one. Thank you.