Excel将所有列与VBA合并

本文介绍了Excel将所有列与VBA合并的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用MS Excel 2010，我有一个excel表，没有宏，没有公式，一切都是原始格式。

I'm using MS Excel 2010 and i have an excel sheet without macro's and without formula's everything is in raw format.

该表包含很多列和行（列A到WC），如下图所示。

The sheet contains a lot of columns and rows (column A to WC) as shown in diagram below.

~ represents split view between columns
# represents row number


          | A | B | C | ~ | AA | AB | ~ | WC |
     -----------------------------------------
    |#   1| x | x | x | ~ |  x |  x | ~ |  x |
    |#  10| x | x | x | ~ |  x |  x | ~ |  x |
    |# 100| x | x | x | ~ |  x |  x | ~ |  x |
    |#1000| x | x | x | ~ |  x |  x | ~ |  x |
    |#2000| x | x | x | ~ |  x |  x | ~ |  x |
    |#3000| x | x | x | ~ |  x |  x | ~ |  x |

我想（合并）将所有列从B移动到WC到最后列A列。

I would like to (merge) move all columns from "B" to "WC" into the last row of column "A".

列A的内容可能不会被丢弃。
每列B至WC必须插入A列中的最后一行

Contents of column "A" may not be discarded.Every column "B" to "WC" has to be inserted below the last row in column "A"

示例（之后）：

          | A | B | C | ~ | AA | AB | ~ | WC |
     -----------------------------------------
    |#   1| x |   |   | ~ |    |    | ~ |    |
    |#  10| x |   |   | ~ |    |    | ~ |    |
    |# 100| x |   |   | ~ |    |    | ~ |    |
    |#1000| x |   |   | ~ |    |    | ~ |    |
    |#2000| x |   |   | ~ |    |    | ~ |    |
    |#3000| x |   |   | ~ |    |    | ~ |    |
    |#4000| x |   |   | ~ |    |    | ~ |    |
    |#5000| x |   |   | ~ |    |    | ~ |    |
    |#6000| x |   |   | ~ |    |    | ~ |    |
    |#7000| x |   |   | ~ |    |    | ~ |    |
    |#8000| x |   |   | ~ |    |    | ~ |    |
    |#9999| x |   |   | ~ |    |    | ~ |    |

我的列故意不包含列标题（列名）。

My columns deliberately do not contain column headers (column-names).

这是最好的方式是什么？

What is the best way of achieving this?

我发现这个线程：
- 和老实说，我现在不能真正了解如何申请我的工作表。其次，这个话题是3年前创建的，而且在两个多月前就已经活跃起来了。在那里我决定在这里提出一个新的问题。

I did found this thread:How to merge rows in a column into one cell in excel? -- and honestly i can't really understand how to apply in on my Sheet at this moment. Secondly that topic was created 3 years ago and was active more than 2 months ago. There for i decided to ask a new question here.

我已经做了研究，发现了很多不同类型的合并方式：

I have done research an found a lot of different types of ways of merging:

> Some recommend CONCATENATE-formula
> Some recommend Transpose formula
> Some recommend macro Some use a VBA script
> Some recommend JOIN function
> Some recommend payed solutions like Kutools

考虑到我的情况下的列数量，我认为一个VBA脚本解决方案将是最合适的。如果有人可以提供反馈意见，我会给一个+1

Considering the amount of columns in my situation i think that a VBA script solution would be most appropriate. If someone could please give feedback i would give a +1

谢谢！

(its now late here) i'm scripting a new macro from scratch that basically does
1. select column B
2. select until top until last row in column B
3. copy contents
4. select column A
5. navigate to last row in column A
6. go 1 cell down
7. paste contents of column B
8. select column B
9. DELETE column B
10. Repeat 600 times :-)

更新2：

这是我自制的宏，循环：

Update 2:

Here is my self-made macro without for-loop:

    Sub CopyColumnBtoAandDeleteB()
Worksheets("Sheet1").Activate
Range("B1", Range("B1").End(xlDown)).Select
Range("B1", Range("B1").End(xlDown)).Copy
Range("A1").End(xlDown).Select
Selection.Offset(1, 0).PasteSpecial xlPasteValues
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
End Sub

糟糕。由于某种原因，这在305707记录之后停止，这是因为我的工作表包含许多活动单元格，导致一个范围内的其他银行单元格，并超出了列中的行超过Excel的内存限制。这是另一个不同的问题。

Oops. for some reason this stops after 305707 records, this was because my sheet contained many active cells, causing additional bank cells in a range and overflowing my rows in column A exceeding the memory limitations of Excel. That's a different issue for another time.