extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel using vba

[malstan]

Hi

I have found an array formula that allows me to select a list of details from a master list in excel based on 2 criteria and referencing a specif column. This array is very slow and time consuming. I have tried googling for something similar using vba but could not find anything similar. I am a novice at vba and find I can help myself if I have a starting point and then I can tweek a macro to suite my needs.

Could somebody perhaps assist with the following.

Start and End value should be selectable and refers to masterlist.

Column refers to 1st column in masterlist.

Value 60 refers to count of items, I am just using simple calculation at this stage eg 'end value -(minus) start value + 1

Values are then extracted into 3 columns as below.

'Sequence", 'Card' & 'Tracking Id

Start Value:	2351047	60
End value:	2351106
Column:	1
Sequence	Card	Tracking
2351047	53 892**********	120940802351047
2351048	533892**********	407640802351048
2351049	533892**********	347940802351049
2351050	533892**********	640940802351050
2351051	533892**********	477540802351051
2351052	533892**********	558640802351052
2351053	533892**********	268140802351053
2351054	533892**********	411540802351054
2351055	533892**********	601740802351055

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[RasGhul]

I understand you would like to use vba but can I ask how many rows or what array formula is being used?

 

[malstan]

I understand you would like to use vba but can I ask how many rows or what array formula is being used?

Hi Thanks for your response.

I found the following array formula online.

=IFERROR(INDEX($B$3:$E$12, SMALL(IF((INDEX($B$3:$E$12, , $D$16)< =$D$15)*(INDEX($B$3:$E$12, , $D$16)>=$D$14), MATCH(ROW($B$3:$E$12), ROW($B$3:$E$12)), ""), ROWS(B20:$B$20)), COLUMNS($A$1:A1)),"")

This works great but is very slow.

my master list im am using is 100000 rows of 3 columns

 

[RasGhul]

Hi Malstan,

From your sample formula I'm assuming your extracting to the same sheet, have a look at my mockup sheet and test 10k rows and see how it goes.

With a table setup the array is dynamic so you can add or remove rows from Columns A-C

You'll need to adjust the ranges to suit your data..

https://www.dropbox.com/s/srgtponkf38uiod/Extract Rows malstan.xlsx?dl=0

 

[malstan]

Hi Malstan,

From your sample formula I'm assuming your extracting to the same sheet, have a look at my mockup sheet and test 10k rows and see how it goes.

With a table setup the array is dynamic so you can add or remove rows from Columns A-C

You'll need to adjust the ranges to suit your data..

https://www.dropbox.com/s/srgtponkf38uiod/Extract Rows malstan.xlsx?dl=0

Hi

once again thank you for your response.

I have tested this with my full data set and the results are immediate. This is amazing.

 

[RasGhul]

Thanks for the feedback