Posts by pike

    Hardly a newbie or treaded badly ... it was excellent advice for getting your thread answered.

    Dave Hawley was not one to fluff around if you couldn't apply the rules and requirements on how to add a title and script your question.

    Hi zMagic,

    faster variation if you have thousands of rows to delete.




    Hi Gilang,

    try the Like operator ...

    Sub GetNumbertest()
        Dim txt As String, mytxt As String
        Dim i As long
        txt = "03619"
        For i = 0 To 9
            If i Like "[!" & txt & "]" Then mytxt = mytxt & i
        Next i
        MsgBox mytxt
    End Sub


    Hi 7absinth,

    Consider something like ....

    addition examples of using Evaluate Function

    Creating Array

    Intersecting Array


    column works.. thinking i was using evaluate function when the rows or columns were dynamic and used [ ] where the array was fixed

    with test array

    Dim dbArray, myrow As Long
        myrow = 5
        With Sheet3
            dbArray = .Range("A1:I20").Value
            .Range("K2").Resize(5, 9) = Application.Index(dbArray, Evaluate("ROW(1:" & myrow & ")"), Application.Transpose([row(1:9)]))
            .[K9].Resize(5, 9) = Application.Index(dbArray, [ROW(6:11)], Application.Transpose([row(1:9)]))
            .Range("K16").Resize(5, 9) = Application.Index(dbArray, Evaluate("ROW(16:20)"), [column(1:9)])
        End With

    with skip lines for the data sets only

    Hi F_Sadr,

    you can use the index function to slice the array .. something like below

    Sub test()
        With Sheet1
            .Cells(1, 1).Resize(1000000, 250) = Application.index(DataCache, Evaluate("ROW(1:1000000)"), Application.Transpose([row(1:250)]))
        End With
        With Sheet2
            .Cells(1, 1).Resize(1000000, 250) = Application.index(DataCache, Evaluate("ROW(1000001:2000001)"), Application.Transpose([row(1:250)]))
        End With
    End Sub



    Hi another way could be to expand on ...