Extract Data From A Large Range Using VBA

Hi,

I've been trying to find a means of doing this all afternoon but I'm struggling to find something that meets my requirement.

Basically I have a spreadsheet which has a very large number of rows (upwards of 900,000) and approx 5-6 columns. The first column contains a reference number in the format 12345/123 and what I'm trying to do is poll the first column looking for instances of a specific reference and, when found, to extract all the columns for that row and write them away as an object.

The thing I'm struggling with is polling the data and extracting the requisite columns - I can do it with a counter which iterates and reads through every cell in the column but this seems a very unoptimised way of doing it and takes far too long. Is there something I can do using range to quickly poll the first column and I can then either call a function or process some code when it finds one which will lift the accompanying data?

Thanks in advance.

Hi Paul,

When you say you'd like to write them away as an object what do you mean?

I think I've just seen something that I can adapt to do what I need :

Please Login or Register  to view this content.

I can pull the data from this point - I think the report will always sort by reference number so I can at least stop it once the reference no longer matches rather than continue polling the whole lot.

Sods law - the minute I post asking I see something

Originally Posted by RichTea88

Hi Paul,

When you say you'd like to write them away as an object what do you mean?

Hi Mate,

That might have been the wrong syntax but basically once I find the correct row I'm going to lift all the data in that row and store it for processing down the line.

I'm still open to better/more efficient ways if anybody has one!

Not sure if this will help,

but if you create a sheet called search results this code will allow you to enter the reference number in format xxxxx/xxx and then finds any results with it and stores it in the search results sheet, from columns A to E.

Please Login or Register  to view this content.

This bit of code clears the previous results you've found. If you don't want to do that you can get rid of it.

Please Login or Register  to view this content.