Handling of very large amount of data in one worksheet

[kkj1]

hi,
I have a large amount of data (168035 * 374) which i want to process. My task is to search some specific record on user input.
e.g., If user is asked to input some number. Then this number will be matched with all A1 (168035 rows)and if the match occurs in multiple places, it should fetch those records (rows) and save it to another worksheet.
I have some code, which is not efficient. when i run the query, excel becomes non responsive (halts). Please give me some suggestion
NOTE: i am using excel-2007
Here is my code:

Sub copying()
Dim i As long, j As long
Dim strsearch As String, lastline As long, tocopy As long
strsearch = CStr(InputBox("Please Enter the video ID: "))
lastline = Range("A1048576").End(xlUp).Row
j = 1
For i = 1 To lastline
    For Each c In Range("A" & i)
        If InStr(c.Text, strsearch) Then
            tocopy = 1
        End If
    Next c
     If tocopy = 1 Then
        Rows(i).Copy Destination:=Sheets(2).Rows(j)
        j = j + 1
    End If
tocopy = 0
Next i
MsgBox "All matching data has been copied."
End Sub

 

[diddi]

can i suggest you try accessing the data externally? i find excel is much more efficient at reading txt files rather than sifting through massive amounts of data as is the case here.

to try it, just save your sheet data as text only

then the code is simple:
assumption: the search text is 6 characters long and consistant in format (easy to change though)

Sub CheckMyData(MySearch as string)
    Dim DataElements, RowCnt as long
    open "C:/MyTextData.txt" for input as #1
    while not eof(1)
        line input #1, MyData
        if left(mydata,6)=mysearch then
            rowcnt=rowcnt+1
            dataelements = split(mydata,chr(9))
               ' dump the data to the sheet in row RowCnt
            for col=1 to ubound(dataelements)
                cells(rowcnt,col)=dataelements(col)
            next col
        end if
    wend
    close
end sub

 

[Jon von der Heyden]

Cross-post: http://www.vbaexpress.com/forum/showthread.php?t=41368

If you are going to cross-post, per the forum rules, please tell us and please also post links to all cross-posts. It saves us spending time unnecessarily on your problem.

Thanks.

 

[diddi]

thx jon.

could you remove my post plz. why help people like this?

 

[kkj1]

Sorry Guys, I had not read that rule, (may be overseen by me quickly). I will not do that again...
Thanks for help.