Vba adding an unnecessary extra row when running macro

A

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
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


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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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:

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

with this:

Code: 
Dte = sWS.Cells(1, j + 1).Value
Amt = sWS.Cells(i, j + 1).Value
 
Upvote 0
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
 
Upvote 0
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...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top