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
452 views

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))
            Next
        
            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
                    Next
    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
778 views
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
3,285 views
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,043 views
+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
1,419 views
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
797 views
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
911 views
0 votes
1 answer

Retrieve epay.info 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
980 views
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
3,331 views
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
1,742 views
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
2,075 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP