Excel VBA create Datarange of filtered records only

[gtaborda]

Hi there, I hope someone can help

I use the following code to copy a whole excel table to a "datarange" , then I loop through it looking for data, etc
I use this datarange for Read only, there is no changes needed to the info.

DataRange = Workbooks("OPENED.XLS").Worksheets("FILESAVED").Range("A2:Z5000").Value

I would like however to create a DataRange, but ONLY with filtered records.
The whole table is too big, and it is taking too long to search for info.

So before I create the Datarange, I would like to filter by one of the columns (1), then the number of records will be much smaller and (I suppose) much faster as the DataRange will be smaller too.

Your help is appreciated

 

[SkipVought]

Hi,

Your array can be assigned (not copied) using the SpecialCells Method...

DataRange = Workbooks("OPENED.XLS").Worksheets("FILESAVED").Range("A2:Z5000")[b].SpecialCells(xlCellTypeVisible)[/b].Value

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gtaborda]

Hi and thanks for replying

Your code seems to work however I now get an error when "processing" the datarange "array"..

The error is on DataRange(l, 1)) (error is "9")

I am not to sure how the "Datarange" asignment works...I thought it creates a copy (in memory) of the data I choose, then I can loop one by one through it....

Here is the code, I will appreciate any comments on what's wrong

lastrow = Workbooks("OPENED.XLS").Worksheets("FILESAVED").Range("A65536").End(xlUp).Row
DataRange = Workbooks("OPENED.XLS").Worksheets("FILESAVED").Range("A2:Z" & lastrow).SpecialCells(xlCellTypeVisible).Value


For l = 1 To lastrow - 1
invdataClient = UCase(DataRange(l, 1))
    If InStr(invdataClient, UCase(clientname)) Then
            Workbooks("Invoicing.XLS").Worksheets("Invoicing").Range("BH" & linea) = DataRange(l, 2)
           Workbooks("Invoicing.XLS").Worksheets("Invoicing").Range("BI" & linea) = DataRange(l, 3)
          linea = linea + 1
    End If
Next l

The loop only runs the first value (1), then stops with the error "9"

thanks

 

[SkipVought]

Once you assign your array, you are no longer working with sheet-related values, like ROWS.

For l = 1 To [b]ubound(DataRange,1)[/b]
invdataClient = UCase(DataRange(l, 1))
    If InStr(invdataClient, UCase(clientname)) Then
            Workbooks("Invoicing.XLS").Worksheets("Invoicing").Range("BH" & linea) = DataRange(l, 2)
           Workbooks("Invoicing.XLS").Worksheets("Invoicing").Range("BI" & linea) = DataRange(l, 3)
          linea = linea + 1
    End If
Next l

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gtaborda]

Hi

I've changed the code to be

For l = 1 To ubound(DataRange,1)

but it only does the loop once...anything else I need to know/change?

 

[SkipVought]

Of course it does. So did your loop.

What do you want it to do?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gtaborda]

basically once the filtered data required is in the "datarange", I want it to go and compare the variable CLIENTNAME

If it is found, then some information from the "datarange" is copied accross to a different sheet

So it needs to "loop" through the datarange as many times as there are records...

In the test I did, there are over 200 "record rows" which are in the "datarange"....but the loop only processes the first one...

Maybe I am not doing it the right way...

 

[SkipVought]

Where do you even mention the variable, CLIENTNAME, in your code?

Where is the list of clientnames? You would loop thru that list with the other loop inside.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gtaborda]

Here

invdataClient = UCase(DataRange(l, 1))
    If InStr(invdataClient, UCase(clientname)) Then

if in the datarange the "clientname" matches, then I want it to do something

This code worked fine, when the whole data sheet was selected (the original code I posted), but now it doesn't
I assume the "filtered records" only option must do something to the datarange and only loops once

Before it looped over 10000 times looking for matching pairs (clientname)

 

[SkipVought]

the array only has values from VISIBLE rows, per your request.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

basically once the filtered data required is in the "datarange", I want it to go and compare the variable CLIENTNAME

If it is found, then some information from the "datarange" is copied accross to a different sheet

So it needs to "loop" through the datarange as many times as there are records...

Can you not use advanced filter or MSQuery to filter for both your existing criteria AND the client name?
Loop through your list of CLIENTNAMEs changing the CLIENTNAME criteria and applying the advanced filter to new location.

The AdvancedFilter output range contains the fieldnames only of the data you want copied across.

Gavin

 

[gtaborda]

SkipVought: Yes, I only wanted the filtered records into the array, however the loop not longer works...
If no filter is applied before transfering sheet data to datarange array, the loop works fine (but much slower than I need because it contains too much data)

Gavona: thanks for your idea about AdvancedFilter (copying data to new location then going through it)
I wanted to use DataRange as I assumed that all data from a sheet goes to an array and, therefore, any loops/searches will be faster but, if cannot find a solution, I'll try AdvancedFilter option.

Obviously my knowledge on VBA is very basic still...

 

[SkipVought]

however the loop not longer works[/quote[

You have not adequately explained HOW the loop is not working.

Perhas you ought to post an example of the data you are filtering on and looping thru.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]