Copy entire column

I have a spread sheet have 300 columns. Some columns have same heading as"Category". I like to copy all the information in one column "Category" without changing the row location. Example:
Raw data:
Test1      Category      Test2      Category
100
      AA
                  PPP
200
      BB
                  MMM
      DD
                  QQQ

After Macro:

Category

AA
PPP

MMM

QQQ

Thanks for the help.

Microsoft ExcelMicrosoft Office

Last Comment

Martin Liss

8/22/2022 - Mon

Ben Personick (Previously QCubed)

4/21/2017

Are you saying you want t select the column, copy it and paste it into another column? you can do this by clicking the row's Label (ie "A" "B" "C" ... "ZZY" "ZZZ") that will highlight the entire column and allow you to ctrl+ c to copy it and then you can similarly click the row label where you want to paste it, and then hit ctrl+v

Norie

4/21/2017

Do you want to combine all the data from the existing 'Category' columns in a single 'Category' column?

ASKER CERTIFIED SOLUTION

Martin Liss

4/21/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Norie

4/21/2017

This will combine the values from all the 'Category' columns into one 'Category' column which is placed to the right of all the existing columns, it assumes you have headers in row 1.

Sub CombineCategoryCols()
Dim rngCat As Range
Dim rngCats As Range
Dim rngDst As Range
Dim rngVal As Range
Dim rngVals As Range

    Set rngDst = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    
    Rows(1).Replace "Category", ""

    rngDst.Value = "Category"
    
    Set rngCats = Rows(1).SpecialCells(xlCellTypeBlanks)
 
    For Each rng In rngCats
        Set rngVals = Range(rng.Offset(1), Cells(Rows.Count, rng.Column).End(xlUp))
        For Each rngVal In rngVals
            If rngVal <> "" Then
                Cells(rngVal.Row, rngDst.Column).Value = rngVal.Value
            End If
        Next rngVal
    Next rng
    
    rngCats.EntireColumn.Delete ' optional
    
End Sub

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Mihir Biswas

4/24/2017

ASKER

His solution works well. Thanks for his quick response.

Martin Liss

4/24/2017

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016