How do I. Transpose rows to columns on excel with use of excel VBA macro

Question

0.00/5 (No votes)

See more:

Hi , I have a similar problem relating to transposing rows to columns wrt to the above.

I applied the VBA code I could only get 1 column instead if 3 columns.

My data table format is like below :

CTR   22/2/22
CTR   28/2/22
CTR   31/3/22

I have over 5000 number of rows to deal with.
I want to able to view as below:

CTR   22/2/22   28/2/22   31/3/22 for various documents

Please can any one help out here?
I'm new to excel VBA.

What I have tried:

VBA

Sub Macro1()
    Dim prevRow As Integer
    Dim currentRow As Integer
    Dim strTemp As String
    Dim pasteColumnIdx As Integer
    
    prevRow = 2 '1st row is for column heading
    currentRow = prevRow + 1 'starts from row 3
    pasteColumnIdx = 8
    
    Do While Cells(currentRow, 1).Text <> ""
    
        If Cells(prevRow, 1).Text = Cells(currentRow, 1).Text Then
            Range(Cells(currentRow, 4), Cells(currentRow, 7)).Select
            Selection.Cut
            Range(Cells(prevRow, pasteColumnIdx), Cells(prevRow, pasteColumnIdx)).Select
            pasteColumnIdx = pasteColumnIdx + 4
            ActiveSheet.Paste
            strTemp = CStr(currentRow) + ":" + CStr(currentRow)
            Rows(strTemp).Select
            Selection.Delete Shift:=xlUp
            'currentRow = currentRow + 1
        Else
            prevRow = prevRow + 1
            currentRow = currentRow + 1
            pasteColumnIdx = 8
        End If

Posted 16-Jan-22 8:36am

Abraham Imarhiagbe

Updated 17-Jan-22 1:05am

Richard Deeming

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-01-17T01:05:00

You should avoid using Copy and Paste - if something else takes the focus while you are doing that then the data will not be pasted into your work book. It's not a robust method and can take a long time.

You should convert the Range of data into an array, transpose the array and then set the value of an appropriate Range to that transposed array.

For example if you have a spreadsheet set up like this

Col A Col B
CTR   22/2/22
CTR   28/2/22
CTR   31/3/22

Use this code to change it to

Col A Col B     Col C     Col D
CTR   22/2/22   28/2/22   31/3/22

and to get rid of the rest of the data

VB

Public Sub demo()

    'Determine how many rows of data you have - that is the number of columns you will want
    Dim lRows As Long
    lRows = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, "A").End(xlUp).Row
    
    'Get the data from the spreadsheet
    Dim arrDemo As Variant
    arrDemo = ThisWorkbook.Sheets(1).Range("B1:B" & CStr(lRows)).Value
    
    'Transpose Array
    Dim arrTransposed As Variant
    arrTransposed = Application.WorksheetFunction.Transpose(arrDemo)
      
    'Write the transposed array back to Excel leaving the first "CTR" in cell A1
    Range("B1").Resize(1, lRows).Value = arrTransposed

    'Clear out the rest of the data
    ThisWorkbook.Sheets(1).Range("A2:B" & CStr(lRows)).ClearContents

    
End Sub

Wendelius · Answer 2 · 2022-01-16T09:01:00

If I understand the question correctly, one way to do this is to select the desired range as you have done, then copy it instead of cutting. After that, use Range.PasteSpecial method (Excel) | Microsoft Docs[^] with the Transpose option set to true.

Once that is done you can delete the rows you don't need anymore and move forward to the next area

How do I. Transpose rows to columns on excel with use of excel VBA macro

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0