Cutting and pasting 90+ columns into 1 columnClosed

Question

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!

beladrian · Accepted Answer

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

Excelguru · Answer

Hi beladrian

instead of CopyRange.Copy Destination:=Range("A" & NewRowA)  use

    CopyRange.Cut
    Range("A" & NewRowA).Select
    ActiveSheet.Paste

Excelguru · Answer

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)

Excelguru · Answer

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

Cutting and pasting 90+ columns into 1 column

4 replies

Subscribe To Our Newsletter!