# Macro to copy Column B contents to Column A, Column C to bottom of Column A, Col C to bottom of Col A etc



## cantante (Dec 20, 2022)

Hey guys,

I'm looking for a macro to copy and paste to Column A, all adjacent columns in batches of 100 columns every time I run the macro

The idea is to slowly be copying and pasting all columns info into column A only

copy and paste col B into Col A
copy and paste col C into Col A
copy and paste col D into Col A
copy and paste col E into Col A
copy and paste col F into Col A
copy and paste col G into Col A

etc..

in a way everytime I run the macro it does 100 columns copy and paste into column A, run macro again copy and paste next 100 columns into bottom of column A and so on

Any thoughts?

Thanks


----------



## HongRu (Dec 20, 2022)

Why in batches of 100 columns ?
Why not do all columns?


----------



## cantante (Dec 20, 2022)

HongRu said:


> Why in batches of 100 columns ?
> Why not do all columns?


It can be all, the thing is that it's 50K columns of 84 rows, so I don't want to cause it to freeze. 

What do you think.


----------



## HongRu (Dec 20, 2022)

cantante said:


> It can be all, the thing is that it's 50K columns of 84 rows, so I don't want to cause it to freeze.
> 
> What do you think.


50k columns?
Excel only has 16384 columns.
And 50k×84=4.2m, Excel only has 1,048,576 rows.


----------



## cantante (Dec 20, 2022)

HongRu said:


> 50k columns?
> Excel only has 16384 columns.
> And 50k×84=4.2m, Excel only has 1,048,576 rows.


Yeah. I didn't remember how many columns exactly but I know 50k was much. Right now I have them transposed ad rows, so I have 50k rows and 84 columns.

If I get 15k rows transposed into columns and then have the macro copy and paste 15k columns into a single column I guess it's will work


----------



## HongRu (Dec 20, 2022)

15k*84=1,260,000 > 1,048,576 rows
Maybe you can do 10K columns and 84 rows at a time.

Try Power Query.
Just to select all colunms and UnpivotOtherColumns will be done.

If your table name "Table1", then

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotOtherColumns = Table.UnpivotOtherColumns(Source , {}, "attr", "value"),
    RemoveColumns = Table.RemoveColumns(UnpivotOtherColumns,{"attr"})
in
    RemoveColumns
```


----------

