I'm trying to scrape website data with Excel VBA

This will grab the entire table on that page.

This project uses early-binding. You will need to set references to:

• Microsoft Internet Controls
• Microsoft HTML Object Library

You can accomplish this within the VBE > Tools > References.

I will say, this site uses a very strange method on setting up their tables, and it was interesting to figure out a decent way to accomplish this.

Also, another thing that you may or may not be okay with is that there are hidden columns in this table that doesn't show on the site but will show in your excel document. If you are not okay with this, you can simply remove or hide them after this code is executed - or if you're up to modifying this to prevent that from happening during execution, more power to you.

Option Explicit

Sub rgnbateamstats()

    Const url$ = "https://rotogrinders.com/team-stats/nba-earned?site=draftkings"

    Dim IE As New InternetExplorer, doc As HTMLDocument
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    With IE
        .Navigate url
        .Visible = True
        ieBusy IE
        Set doc = .Document
    End With
    
    Dim r As Long, c As Long, tCol As HTMLDivELement
    Dim subTbls(): subTbls = Array("rgt-bdy left", "rgt-bdy mid", "rgt-bdy right")
    
    Dim subTbl As Long        
    For subTbl = 0 To 2
        For Each tCol In getSubTblCols(doc, subTbls(subTbl)).getElementsByClassName("rgt-col")
            c = c + 1
            For r = 1 To tCol.getElementsByTagName("div").Length
                ws.Cells(r, c) = tCol.getElementsByTagName("div")(r - 1).innerText
            Next
        Next tCol
    Next subTbl

End Sub

Private Function getSubTblCols(doc As HTMLDocument, ByVal className$) As HTMLDivElement
    Dim tbl As HTMLTable
    Set tbl = doc.getElementById("proj-stats")
    Set getSubTblCols = tbl.getElementsByClassName(className)(0).Children(0). _
            Children(1)
End Function

Private Sub ieBusy(ieObj As InternetExplorer)
    With ieObj
        Do While .Busy Or .ReadyState < READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
End Sub

Okay, time to attempt what's going on here.

There are three sub-tables in your table. This is the best way to explain it, but this means you will first loop through each sub table with this line:

For subTbl = 0 To 2

Within that loop, you will loop that sub-table's columns with this line:

For Each tCol In getSubTblCols(doc, subTbls(subTbl)).getElementsByClassName("rgt-col")

rgt-col is the class name for the column in each table - so at least that part was easy. The function getSubTblCols grabs the main sub-table element class name of one of the three names of the sub table in the array subTbls().

c is your Excel column number, r is the row number. You also use r for each of the HTML's row numbers, but it's uses base 0, so you have to subtract 1.

Then get the cell's value using the innerText property of the cell, place that into your spreadsheet, then rinse and repeat.

I moved your busy webpage function to a new sub, ieBusy. I also added the .readyState property because as I stated in my comment that .busy by itself is unreliable at best.

Try this part for extracting first column

Set allrowofdata = appIE.document.getElementById("proj-stats")

Set newobj = allrowofdata.getElementsByClassName("rgt-col")(0)

For Each x In newobj.Children
r = r + 1
Cells(r, 1).value = x.innerText
Next x