Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 15th, 2007, 04:50 PM
Registered User
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to work through huge amounts of data effective

I have 2 excel sheets. One contains userid's and related fields, the other contains userid's and home-drive storage usage. They need to be combined in one sheet.

The obvious way is to loop through one and search in the other for the matching userid, then supplement either one and move on to the next. This will take ages tho and looks both ugly and inefficient.

Is there someone here who could point me in the right direction to go about it more efficient ?

I would be happy to post relevant code if it helps... but it's not that my code is broke, it just takes ages to loop through 90000 records till the record I need is found.

I'm aware that this should be done in a database, but for some reason managers prefer excel sheets where I work.

I was thinking about importing to an access database, do the combining there and then convert back to excel, but I rather take the opportunity to learn about sorting insane amounts of data :)

Thanks in advance.
Old January 16th, 2007, 01:10 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


If you are loping thru the cells to find a match it will take time. On the other hand, try to use the find function.


Set rFnd = Workbook.Sheets(1).Range("A1:A60000").Find(what:=s USerID, Lookat:=xlPart)

This will get you the range if the text is found or will be set to nothing

Hence you can check

IF Not rFnd Is Nothing
   CElls(rFnd.row,2).value = sSomeValue
  ' Text Not Found
End if


Old January 16th, 2007, 08:12 AM
Registered User
Join Date: Jan 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for the info. That does the trick for me.

Is there any search pattern associated with the Find function so that I could try and learn a bit about effective searching ?



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to do effective testing on WCF SendilKumar Windows Communication Foundation 1 July 31st, 2009 04:02 AM
View returns huge data,taking more time gunjan.sh SQL Server 2005 15 April 9th, 2008 05:51 AM
Representing Huge Amount of Data In a Html file me_zeta HTML Code Clinic 7 November 6th, 2006 11:23 PM
How to make high effective use of MSXML obj CharlieHower XSLT 9 March 27th, 2006 02:33 AM
Storing Large Amounts Of Data TSEROOGY Classic ASP Databases 3 October 26th, 2004 02:12 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.