Previous Topic Next Topic

Cutting and pasting 90+ columns into 1 column

[Closed]
Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 -
Hello,

I'm compiling a list of server software and the original data comes in a sheet with 90+ columns of data.
I would like to combine all these columns into a single column so that I can pivot off it.
These columns have some blank values.

I've created a copy paste macro but would like it to cut instead of copy.
I would also like it to loop till it finds the last column with any data instead of me trying to state do for column b then c then d etc.

Sub CombineColumns()

'Copy column b to Column a
Columns("b").Copy Destination:=Columns("A")

NewRowA = Range("A" & Rows.Count).End(xlUp).Row + 1
LastRowB = Range("c" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("c1:c" & LastRowc)
CopyRange.Copy Destination:=Range("A" & NewRowA)

NewRowA = Range("A" & Rows.Count).End(xlUp).Row + 1
LastRowc = Range("d" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("d1:d" & LastRowd)
CopyRange.Copy Destination:=Range("A" & NewRowA)

End Sub

Thanks in advance for your help!

4 replies

Answer 1 / 4

Hi Excelguru,

Thanks for your prompt reply!

Is this the way it should look?
I am getting a runtime error.
Would this loop through all the other other 90+ columns in that sheet automatically?

Thanks again in advance!


Sub CombineColumns()

'Copy column b to Column a
Columns("b").Copy Destination:=Columns("A")

NewRowA = Range("A" & Rows.Count).End(xlUp).Row + 1
LastRowB = Range("c" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("c1:c" & LastRowc)
CopyRange.Cut
Range("A" & NewRowA).Select
ActiveSheet.Paste

NewRowA = Range("A" & Rows.Count).End(xlUp).Row + 1
LastRowc = Range("d" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("d1:d" & LastRowd)
CopyRange.Cut
Range("A" & NewRowA).Select
ActiveSheet.Paste

End Sub
4
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Answer 2 / 4
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 307
Hi beladrian

instead of CopyRange.Copy Destination:=Range("A" & NewRowA) use 

    CopyRange.Cut
    Range("A" & NewRowA).Select
    ActiveSheet.Paste
Answer 3 / 4
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 307
Hi beladrian

You have made a spelling mistake in the code
LastRowc = Range("d" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("d1:d" & LastRowd)
Report

What words are spelt wrong? You just wrote the exact same thing he did.
Answer 4 / 4
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 307
LastRowc = Range("d" & Rows.Count).End(xlUp).Row
Set CopyRange = Range("d1:d" & LastRowd)

I just meant that instead of LastRowc he used LastRowd in the second line.
This caused the error as excel could not find the value of LastRowd and hence it took the value as zero
There is no zeroth row or column in excel
Ask a question