Need Vba help?

Question

I want to take the column name of each column and associate that with the value under that column name. Each column will be different number of rows.  There are x number of columns. So if the first column is D, the column name is let's say fruits and D2 is apple and D3 is banana.  I want to have in A1 and A2 fruits and B1 as Apple and B2 be banana.  Do it until all value have been first column is copied over to columns A and B, then move on to column E until all columns are done. Is there a way to do it excel without vba? If not, can you please provide the macro to this? Thanks in advance.

BT_Bot · Accepted Answer

To do this without VBA, because of the non-constants (number of columns, number of rows per column) I guess you would just have to manually copy/paste. If you can't find a non-VBA solution, here's some code:

Sub FruitA()
    Application. ScreenUpdating = False
    Dim myCol, myRow, cnt As Integer
    cnt = 0
    For myCol = 4 To ActiveSheet. UsedRange. SpecialCells( xlCellTypeLastCell). Column
        For myRow = 2 To ActiveSheet. UsedRange. SpecialCells( xlCellTypeLastCell). Row
            If Cells(myRow, myCol) <> "" Then
                cnt = cnt + 1
                Cells(cnt, 1) = Cells(1, myCol)
                Cells(cnt, 2) = Cells(myRow, myCol)
            End If
        Next myRow
    Next myCol
    Application. ScreenUpdating = True
    MsgBox (cnt & " item(s) found within " & myCol - 3 & " column(s)")
End Sub

Trending News

Need Vba help?

1 Answer