0

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

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 |

I would like to (merge) move all columns from "B" to "WC" into the last row of column "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"

Example (after):

          | 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?

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

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

Thanks!

Updates:

(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 :-)

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

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.

Community
  • 1
  • 1
paul
  • 23
  • 2
  • 9
  • If you are discarding the value from column A then a user defined function (VBA) using `.Join`, the `CONCATENATE` function or simply stringing them together with ampersands (e.g. `=B2&C2&D2...`) is fine. If you are looking to include the value from column A (contrary to your description) then a macro (VBA) using `.Join` or a *helper* column would be best. –  Mar 08 '15 at 20:16
  • "column A" may not be discarded. So every column "B" to "WC" has to be inserted below the last row in "column A" etc. – paul Mar 08 '15 at 20:21
  • It's not clear to me if you are merging the contents of B1:WC1 into one cell beneath the last row of column A then B2:WC2 beneath that, etc; or if you are putting all of column B beneath A, then column C beneath the new end of column A, etc. – Porcupine911 Mar 08 '15 at 20:32
  • Ok, yes i understand. Maybe i was not clear with my post. every column contents has to be moved to the last row of column "A" so i would get a single column. I have added the end result to the topic. Please have a look again, thanks – paul Mar 08 '15 at 20:34
  • see if this solution works for you: [Convert Multiple Columns to One Large Column (Excel 2010)](http://stackoverflow.com/questions/13073033/convert-multiple-columns-to-one-large-column-excel-2010) – Porcupine911 Mar 08 '15 at 20:51
  • The last solution by specialscope, came closest. However i think his code is wrong because it begins parsing at A2 and after a couple seconds shows "Runtime error '1004' Possible reasons 1) Copy and paste areas cannot overlap unless they are the same size and shape. 2) If you are using the Create from Selection command, the row or column containing the proposed names will not be included in the.... (cant read any more) but the debugger navigates to Line #16 and #17 .Range(rCell, .Cells(lRows, rCell.Column).End(xlUp)).Cut _ wsNew.Cells(lRows, 1).End(xlUp)(2, 1) – paul Mar 08 '15 at 21:28

3 Answers3

0

This is a sort of Tetris approach. It may not be the quickest but turning off screen updating and event handling should speed it up. Calculation mode may also be an option to set to xlCalculationManual if there are formulas involved.

Sub from_A_to_WC()
    Dim c As Long, grp As Long, cols As Long
    apps_Toggle
    With Sheets("Sheet3")
        grp = .Cells(Rows.Count, 1).End(xlUp).Row
        cols = .Columns("WC").Column
        For c = 2 To cols
            .Cells(1, 2).Resize(grp, cols).Insert Shift:=xlDown
            .Cells((c - 1) * grp, 1).Offset(1, 0).Resize(grp, 1).Delete Shift:=xlToLeft
        Next c
    End With
    apps_Toggle
End Sub

Sub apps_Toggle()
    Application.ScreenUpdating = Not Application.ScreenUpdating
    Application.EnableEvents = Not Application.EnableEvents
End Sub

Change the worksheet name in the third line of you need to and adjust the boundary column (currently column WC) in the 5th if that doesn't match your data.

It really isn't an appropriate solution if you have anything other than the relevant data on the worksheet as it will greatly displace anything that isn't being moved.

  • Thanks Jeeped. It looks very promising. However when i tried your script on a test grid which contained empty cells. Then the empty cells are also moved to "left". I'm able to filter empty cells out with the Excel "Custom Filter". But would there be any chance of tweaking the Tetris approach? – paul Mar 09 '15 at 08:25
  • I'm sorry to inform you Jeeped. Your script says "Excel cannot complete this task with available resources. Choose less data or close other applications. (This was done whilst running on the Worksheet with 601 columns and 2704 rows of data) – paul Mar 09 '15 at 08:31
  • @paul - That's a shame as it worked so well on a smaller data set. I guess all of that block range inserting and deleting takes its toll on a machine. –  Mar 09 '15 at 08:55
  • Yup, i will try and make just a simple VBA script with "select", "cut" and "paste" and "delete" option. Might not be quickest but simple and effective. If i can get that in a loop for 601 times i'm very happy. – paul Mar 09 '15 at 09:06
  • I have added a VBA macro i wrote myself that only requires a for-loop – paul Mar 09 '15 at 09:34
  • i have just discovered the issue of Blank cells -- as my Sheet contained many active cells. Whenever i used a macro for a selection it would grap active cells and migrate them to my column A. – paul Mar 09 '15 at 19:13
0

Here's an approach using arrays to speed things up.

Option Base 1 ' Don't omit this line!
Sub ArrayMan()

    Dim u As Long
    Dim v As Long
    Dim k As Long: k = 1
    Dim z As Long

    InArray = Range("A1:WC400").Value 'Change to your actual dimensions

    u = UBound(InArray, 1)
    v = UBound(InArray, 2)
    z = u * v

    Dim OutArray() As Variant
    ReDim OutArray(z)

    For i = 1 To v 'columns
        For j = 1 To u 'rows
            OutArray(k) = InArray(j, i)
            k = k + 1
        Next
    Next
    Range("A1:A" & z) = WorksheetFunction.Transpose(OutArray)

End Sub
Porcupine911
  • 928
  • 9
  • 22
  • I have tried your script as it looked promising. I did a test on a grid filled with data from A1:H16 and that worked great. However the real data worksheet has a couple of empty cells in different columns and therefor is showing an Run-Time error 13 "Type Mismatch" -- i guess. Is there anything we could do about that? Great work anyway! – paul Mar 09 '15 at 08:21
  • I have added a VBA macro i wrote myself that only requires a for-loop – paul Mar 09 '15 at 09:35
  • Glad you found a solution. For future reference, an array solution like this one will be much faster. By the way, I had no problem with empty cells on my end. – Porcupine911 Mar 09 '15 at 15:52
  • The reason some script examples might not have worked out (as i have just discovered) was because my excel sheet contained "Many active cells" which eventually showed as blank cells while i was copying/merging column cells :| - http://support.microsoft.com/kb/244435 and i still have not found a solution for that, perhaps i need to copy to a new worksheet – paul Mar 09 '15 at 18:57
0

This tiny macro code worked for me:

    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

Unfortunately this it stops after 305707 records because of limitations of MS Excel 2010, i recommend that if someone else uses it you should get rid of a couple of columns because 255 is a lot of columns (even for MS Access). You should also run MS Excel in Safe mode.

paul
  • 23
  • 2
  • 9