Return row numbers in Excel VBA - SQL
07:40 24 Aug 2026

Using Excel (2010 - 64 Bit) VBA ADO/SQL with the following code, I am merging data from 2 sheets ASDRT and FSR and dumping the result set into sheet RESULT. No problem with the code....

Sheet ASDRT:

ITEM ID QTY PRICE TOTAL
1 Q8 20W50 4X4L EU 100,00 35,00 3.500,00
2 Q8 10W40 4X20L BL 200,00 70,00 14.000,00
3 ENI 10W40 4X20L BL 50,00 60,00 3.000,00
4 Q8 15W40 4X5L BL 50,00 50,00 2.500,00
5 Q8 5W30 208L IT 10,00 700,00 7.000,00
6 ENI 5W30 208L IT 10,00 725,00 7.250,00
7 ENI 10W40 4X4L IT 5,00 30,00 150,00
8 CAS 10W40 4X4L IT 5,00 23,00 115,00
TOTAL 430,00 37.515,00

Sheet FSR:

ITEM ID QTY PRICE TOTAL
1 Q8 15W40 4X5L BL 20,00 45 900,00
2 ENI 10W40 4X20L BL 10,00 75 750,00
3 Q8 10W40 4X20L BL 10,00 77 770,00
4 CAS 5W30 208L IT 10,00 750 7.500,00
5 ENI 10W40 4X4L IT 5,00 35 175,00
6 ENI 5W30 208L IT 20,00 740 14.800,00
7 CAS 10W40 4X4L IT 10,00 20 200,00
TOTAL 85,00 25.095,00

And, the code i am using is;

Sub Test2()
    Dim adoCN As Object, RS As Object
    Dim myFile As String, strSQL As String, LastRow As Integer
        
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adOpenStatic = 3
    Const adCmdText = 1
    
    myFile = ThisWorkbook.FullName
    
    Set adoCN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    
    adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
    adoCN.Properties("Data Source") = myFile
    adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=Yes; IMEX=1"
    adoCN.Open
             
    strSQL = " Select [ID], Sum([QTY]) As myQTY,  Round(myTOTAL/myQTY,2) As myPRICE, Sum([TOTAL]) As myTOTAL From " & _
             " ( " & _
             "   Select [ID], [QTY], [TOTAL] From [ASDRT$] " & _
             "   Union All " & _
             "   Select [ID], [QTY], [TOTAL] From [FSR$] " & _
             " ) " & _
             " Where [ID] <>'' Group By [ID]"
    
    RS.Open strSQL, adoCN, adOpenStatic, adLockReadOnly, adCmdText
    
    Sheets("Result").Range("F1:J" & Rows.Count) = ""
    Sheets("Result").Range("F1:J1") = Array("ITEM", "ID", "QTY", "AV. PRICE", "TOTAL")
    
    Sheets("Result").Range("G2").CopyFromRecordset RS
    
    RS.MoveFirst
    
    LastRow = 1 + RS.RecordCount
    
    Sheets("Result").Range("G" & LastRow + 1) = "TOTAL"
    Sheets("Result").Range("H" & LastRow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    Sheets("Result").Range("J" & LastRow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    Sheets("Result").Range("F2:F" & LastRow).FormulaArray = "=ROW()-1"
    
    RS.Close
    adoCN.Close

    Set RS = Nothing
    Set adoCN = Nothing
End Sub

By using the code above, the generated RESULT sheet is;

ITEM ID QTY AV. PRICE TOTAL
1 CAS 10W40 4X4L IT 15 21 315
2 CAS 5W30 208L IT 10 750 7500
3 ENI 10W40 4X20L BL 60 62,5 3750
4 ENI 10W40 4X4L IT 10 32,5 325
5 ENI 5W30 208L IT 30 735 22050
6 Q8 10W40 4X20L BL 210 70,33 14770
7 Q8 15W40 4X5L BL 70 48,57 3400
8 Q8 20W50 4X4L EU 100 35 3500
9 Q8 5W30 208L IT 10 700 7000
TOTAL 515 62610

What i want to learn is; how to get the row numbers by using a subquery or whatever so, in the SQL statement instead of what i've used as;

Sheets("Result").Range("F2:F" & LastRow).FormulaArray = "=ROW()-1"

excel vba