Vba adding an unnecessary extra row when running macro

[ANTONIO1981]

HI ALL

I Have the following data in A1

[TABLE="width: 625"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD]SITE[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[TD] 1.144[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]AT3[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[TD] 1.154[/TD]
[/TR]
[TR]
[TD]AT7[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[TD] 1.211[/TD]
[/TR]
[TR]
[TD]AT19[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[TD] 1.328[/TD]
[/TR]
[TR]
[TD]AT25[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[TD] 1.197[/TD]
[/TR]
[TR]
[TD]AT26[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[TD] 1.320[/TD]
[/TR]
[TR]
[TD]AT28[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[TD] 1.269[/TD]
[/TR]
[TR]
[TD]AT30[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[TD] 1.174[/TD]
[/TR]
[TR]
[TD]AT31[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[TD] 1.152[/TD]
[/TR]
[TR]
[TD]AT32[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[/TR]
[TR]
[TD]AT33[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[TD] 1.388[/TD]
[/TR]
[TR]
[TD]AT34[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[TD] 1.171[/TD]
[/TR]
[TR]
[TD]AT36[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[TD] 1.159[/TD]
[/TR]
[TR]
[TD]AT39[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[TD] 1.457[/TD]
[/TR]
[TR]
[TD]AT41[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[TD] 1.443[/TD]
[/TR]
[TR]
[TD]AT42[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[TD] 1.391[/TD]
[/TR]
[TR]
[TD]AT43[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[TD] 1.400[/TD]
[/TR]
[TR]
[TD]AT44[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[TD] 1.302[/TD]
[/TR]
[TR]
[TD]AT45[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[TD] 1.263[/TD]
[/TR]
[TR]
[TD]AT48[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[TD] 1.286[/TD]
[/TR]
[TR]
[TD]AT40[/TD]
[TD] 1.324[/TD]
[TD] 1.324[/TD]
[TD] 1.324[/TD]
[TD] 1.324[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[TD] 1.406[/TD]
[/TR]
[TR]
[TD]AT35[/TD]
[TD] 1.547[/TD]
[TD] 1.549[/TD]
[TD] 1.549[/TD]
[TD] 1.549[/TD]
[TD] 1.515[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[TD] 1.363[/TD]
[/TR]
[TR]
[TD]AT47[/TD]
[TD] 1.156[/TD]
[TD] 1.156[/TD]
[TD] 1.156[/TD]
[TD] 1.156[/TD]
[TD] 1.156[/TD]
[TD] 1.156[/TD]
[TD] 1.537[/TD]
[TD] 1.537[/TD]
[TD] 1.537[/TD]
[TD] 1.537[/TD]
[TD] 1.537[/TD]
[TD] 1.537[/TD]
[/TR]
[TR]
[TD]AT49[/TD]
[TD] - [/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[/TR]
[TR]
[TD]AT50[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[TD] 1.511[/TD]
[/TR]
[TR]
[TD]AT51[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1.388[/TD]
[/TR]
[TR]
[TD]AT52[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1.346[/TD]
[TD] 1.346[/TD]
[TD] 1.346[/TD]
[TD] 1.346[/TD]
[TD] 1.346[/TD]
[/TR]
[TR]
[TD]AT38[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] 1.266[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]AT17[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] 1.108[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]




APPLYING THE FOLLLOWING CODE

Public Sub Convert_commission_into_final_table()
Dim sWS     As Worksheet, _
    dWS     As Worksheet
    
Dim i       As Long, _
    j       As Long, _
    rowx    As Long
    
Dim LR      As Long


Dim Site    As String, _
    Dte     As Date, _
    Amt     As Double


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


Set sWS = Sheets("COMMISION")
Set dWS = Sheets("COMMISSION_table")


dWS.Range("A1").Value = "Site"
dWS.Range("B1").Value = "Month"
dWS.Range("C1").Value = "Commission"


LR = sWS.Range("A" & Rows.Count).End(xlUp).Row
rowx = 2


For i = 2 To LR
    'Collect Site and Cost names
    Site = sWS.Range("A" & i).Value


    For j = 1 To 12
        'Collect Date and Amount
        Dte = sWS.Cells(1, j + 2).Value
        Amt = sWS.Cells(i, j + 2).Value
        
        'Store Values in new table
        dWS.Range("A" & rowx).Value = Site
        dWS.Range("B" & rowx).Value = Dte
        dWS.Range("C" & rowx).Value = Amt
        rowx = rowx + 1
    Next j
Next i


With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

im getting always an extra row


[TABLE="width: 202"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Site[/TD]
[TD]Month[/TD]
[TD]Commission[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/09/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]01/12/2018[/TD]
[TD="align: right"]1.1444[/TD]
[/TR]
[TR]
[TD]AT1[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 202"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]AT2[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/09/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]01/12/2018[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AT2[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


how can i correct this code?

thanks in advance

Anthony

 

[nardagus]

Hello,

You don't get an extra row. You get exactly 12 rows you needed. However you code starts second loop from 3rd column, not from 2nd. Look that dates in second table start from February.
Without testing your code I think it would suffice to change
this:

Dte = sWS.Cells(1, j + 2).Value
Amt = sWS.Cells(i, j + 2).Value

with this:

Dte = sWS.Cells(1, j + 1).Value
Amt = sWS.Cells(i, j + 1).Value

 

[Scott T]

You are looping for 12 months but you are only listing 11 months your output starts in February not Jan.


[TABLE="class: cms_table, width: 202"]
<tbody>[TR]
[TD]AT1
[/TD]
[TD="align: right"]01/02/2018
[/TD]
[TD="align: right"]1.1444

[/TD]
[/TR]
</tbody>[/TABLE]

I think you want J+1 instead of j + 2

 

[nardagus]

P.s. I didn't think this through so I might be blabbing, but it seems you have fixed amount of columns. Didn't you think about transposing the data? It would speedup your code as you get rid of one loop. Just paste AT1 12 times, then transpose columns headers, then transpose values, then next loop...

 

[ANTONIO1981]

many thanks to both ! it is working ok!!!