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?

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!

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

Sub ProcessData()

Dim rng As Range
Dim rngData As Range

Counter = 0
Set rngData = Range("A:A")

For Each rng In rngData
    If rng.value = strName Then MsgBox ("Found It" & Counter & " Times")
Next rng

End Sub

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

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.

Sub Search()
   Application.ScreenUpdating = False
   Dim row_count, myCode As String
   myCode = InputBox("Enter Reference Number xxxxx/xxx:", "Search")
   With Sheets("Search Results")
      .Rows(2 & ":" & .Rows.Count).Delete
   End With

   For row_count = 1 To Range("A" & Rows.Count).End(xlUp).Row
      If InStr(1, Range("A" & row_count), myCode) > 0 Then
         Range("A" & row_count & ":F" & row_count).Copy _
               Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
      End If
   Next
   Application.ScreenUpdating = True
   Sheets("Search Results").Activate
End Sub

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.

   With Sheets("Search Results")
      .Rows(2 & ":" & .Rows.Count).Delete
   End With