Search : in
By :

Cutting and pasting 90+ columns into 1 column

Last answer on Aug 29, 2009 9:34:03 am BST beladrian, on Jun 26, 2009 4:01:06 am BST 
 Report this message to moderators

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!

Configuration: Windows 2000 Internet Explorer 6.0

Best answers for « Cutting and pasting 90+ columns into 1 column » in :
Combine 7 excel's into 1 excel Show
Transforming columns into lines Show Transforming columns into lines Example Limitations It is endemic for most Linux tools to work with lines, but not with columns (sed, awk, grep, etc..). However, it may happens,that you have a file where the data should be read in...
Copy data into next empty cell in new sheet Show
Converting your PDF file into an Image ShowConverting your PDF file into an Image Universal Document Converter PDF2IMAGE PDF TO IMAGE CONVERTER You could have encountered difficulties to modify PDF docs in the past. Nowadays this is no more trouble as there are several PDF...
Assembling multiple PDF files ShowAssembling multiple PDF files Intro Steps: Intro PDF creator is a software allowing you to print several files into one with its printer function. Download link http://en.kioskea.net/telecharger/telecharger-40-pdf...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Download PDF Creator ShowSupports Windows 7 Beta PDFCreator is a free tool to create PDF files from almost any application that can print. It also possesses options of security allowing to code the PDF to protect their reading. PDF Creator also allows to create images...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

Excelguru, on Jun 27, 2009 1:59:04 pm BST

Hi beladrian

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

    CopyRange.Cut
    Range("A" & NewRowA).Select
    ActiveSheet.Paste
Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru

2

beladrian, on Jun 29, 2009 1:37:19 am BST

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

   
Reply to beladrian

3

Excelguru, on Jun 29, 2009 9:23:07 am BST

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) Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru

4

tsax, on Aug 28, 2009 1:28:45 pm BST

What words are spelt wrong? You just wrote the exact same thing he did.

   
Reply to tsax

5

 Excelguru, on Aug 29, 2009 9:34:03 am BST
  • +1

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
A wise man once said, 'I complained that I had no shoes until I met a man who had no feet.'
I am interested in financial Modelling and custom excel development with excel macros.

   
Reply to Excelguru
Ask your question Reply
They need your help