How to do multiple transpose in excel vba

0 votes

I need all the things on a list that is dispersed into one column. The scattered items can be arranged into one column by placing them inside the empty cells.

enter image description here

This is what I demand. The first column's values can't move from their original positions. I have a code that performs the transposition, but it rearranges the values in the first column by grouping them all together. As a result, the position of pink, which is ninth, is changed to eighth because the blank is ignored.

Sub test3()
  Dim outarr()
  Nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  inarr = Range(Cells(1, 1), Cells(lr, Nc))
  ReDim outarr(1 To lr * Nc, 1 To 1)
    indi = 1
    For i = 1 To UBound(inarr, 1)
        For j = 1 To UBound(inarr, 2)
            If inarr(i, j) <> "" Then
             outarr(indi, 1) = inarr(i, j)
             indi = indi + 1
            End If
        Next j
   Next i
  Range(Cells(1, Nc + 1), Cells(indi - 1, Nc + 1)) = outarr
End Sub

My requirement is to move the values from other columns without disturbing the 1st column.

Jan 22, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

Use Dictionary object

Sub test2()

    With New Scripting.Dictionary
        Dim cel As Range
            For Each cel In Range("A1", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
                .Add cel.Row, Range(cel, Cells(cel.Row, Columns.Count).End(xlToLeft))
            Dim lastCol As Long
                lastCol = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                Dim ik As Long
                    For ik = 0 To .Count - 1
                        Cells(.keys(ik), lastCol + 2).Resize(, .Items(ik).Columns.Count).Value = .Items(ik).Value
    End With
End Sub

just add reference to "Microsoft Scripting Runtime" library

enter image description here enter image description here

answered Jan 22, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,700 points
+1 vote
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Transposing columns to rows in Excel

Due to the amount of your data, ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP