Efficiently turn large string into data fields using delimiters

[fishstar0]

Hello, I am trying to create as efficient a subroutine as possible to deconcatenate a large string based on predetermined delimiters and add the data into a place power query can access

I have several different tables of data which I am grabbing for and I want one versatile subroutine which I can pass the delimiters as arrays into

I am aware in advance how many rows I will end up with

My current main difficulty is that my expected table size is typically over 10000 lines and sometimes even larger making efficiency a bottleneck for the programs functionality

My questions are
1. Is there a more efficient way of getting the value between two known delimiters?
2. Is there a more efficient way of transferring to Power Query than an excel table?
3. Is there any way to further simplify this code?

Sub ExtractData(LeftDelimiter() As String, RightDelimiter() As String,source as string, TotalRows as integer, ExcelDataTable as ListObject)

    Dim FirstSplit(TotalRows) As String

    ExcelDataTable.DataBodyRange.ClearContents
    ExcelDataTable.DataBodyRange.Rows.Delete
    
    For tableColumn = LBound(LeftDelimiter) To UBound(LeftDelimiter)
        RightCut() = Split(source, LeftDelimiter(tableColumm))

        For Row = 1 To UBound(RightCut) 'Because of the nature of the input, I don't want the first cut
            ExcelDataTable.ListRows.Add.Range(tableColumn + 1) =  "'" + Left(RightCut(Row), InStr(RightCut(Row), RightDelimiter(TableColumn) - 1)
        Next Row
    Next tableColumn

End Sub

 

[RayFrye]

VBA Code looks tight to me, not much you can do when dealing with a large amount of data.

 

[alansidman]

Using XL2BB, would you upload a sample of your data and a mocked up solution so that we can play with the data in Power Query so that we can provide you with a solution that will speed things up. PQ is designed to handle large datasets quicklyl