Previous Topic Next Topic

Cutting and pasting 90+ columns into 1 column

Closed
beladrian - Jun 25, 2009 at 11:01 PM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Aug 29, 2009 at 04:34 AM
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!
Related:

4 replies

Answer 1 / 4
beladrian
Jun 28, 2009 at 08:37 PM
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
Answer 2 / 4
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 27, 2009 at 08:59 AM
Hi beladrian

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

    CopyRange.Cut
    Range("A" & NewRowA).Select
    ActiveSheet.Paste
1
Answer 3 / 4
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 29, 2009 at 04:23 AM
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)
1
tsax
Aug 28, 2009 at 08:28 AM
What words are spelt wrong? You just wrote the exact same thing he did.
0
Answer 4 / 4
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 29, 2009 at 04:34 AM
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
1