CODEWITHSUNDEEP
vbaexcelpivot-table
Elbert
Elbert

Reputation: 506

VBA Pivoting format

I have excel like the following

   A    |        B
1 aaa   |     123555
2 aaa   |     234555
3 bbb   |     345555
4 bbb   |     444555
5 bbb   |     555555

And I would like to make it have format like pivot, and it is precisely below the last row:

    A
6  aaa
7  123555
8  234555
9  bbb
10 345555
11 444555
12 555555

For this I use looping

NextRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

for i = 1 to 5
   Cells(i + NextRow,A).value = cells(i,A).value
    NextRow = NextRow + 1

      for j = 1 to 5
       if cells(j,A).value = "aaa" Then
       cells(i + Nextrow, A) = Cells (j,2).value  
       Nextrow = NextRow + 1

       end if  
       next j
next i

The NextRow is to make sure that it is moving to the next row. But I got error since the result is only :

   A
6 aaa
7 bbb

Any solution in this code ? Thanks

Upvotes: 0

Views: 50

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

Try this

Sub T()

    Dim sht As Worksheet, NextRow As Long, CurrVal, PrevVal
    Dim i As Long

    Set sht = Worksheets("Sheet1")

    NextRow = sht.Range("A" & Rows.Count).End(xlUp).Row + 1
    PrevVal = "~~~~~~~~~~~~"

    For i = 1 To NextRow - 1

        CurrVal = sht.Cells(i, "A").Value
        If CurrVal <> PrevVal Then
            sht.Cells(NextRow, "A").Value = CurrVal
            PrevVal = CurrVal
            NextRow = NextRow + 1
        End If
        sht.Cells(NextRow, "A").Value = sht.Cells(i, "B").Value
        NextRow = NextRow + 1

    Next i


End Sub

Upvotes: 2

Related Questions