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


[TABLE="width: 317"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Start Value:[/TD]
[TD="align: right"]2351047[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]End value:[/TD]
[TD="align: right"]2351106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column:[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sequence[/TD]
[TD] Card[/TD]
[TD]Tracking[/TD]
[/TR]
[TR]
[TD="align: right"]2351047[/TD]
[TD]53 892**********[/TD]
[TD]120940802351047[/TD]
[/TR]
[TR]
[TD="align: right"]2351048[/TD]
[TD]533892**********[/TD]
[TD]407640802351048[/TD]
[/TR]
[TR]
[TD="align: right"]2351049[/TD]
[TD]533892**********[/TD]
[TD]347940802351049[/TD]
[/TR]
[TR]
[TD="align: right"]2351050[/TD]
[TD]533892**********[/TD]
[TD]640940802351050[/TD]
[/TR]
[TR]
[TD="align: right"]2351051[/TD]
[TD]533892**********[/TD]
[TD]477540802351051[/TD]
[/TR]
[TR]
[TD="align: right"]2351052[/TD]
[TD]533892**********[/TD]
[TD]558640802351052[/TD]
[/TR]
[TR]
[TD="align: right"]2351053[/TD]
[TD]533892**********[/TD]
[TD]268140802351053[/TD]
[/TR]
[TR]
[TD="align: right"]2351054[/TD]
[TD]533892**********[/TD]
[TD]411540802351054[/TD]
[/TR]
[TR]
[TD="align: right"]2351055[/TD]
[TD]533892**********[/TD]
[TD]601740802351055[/TD]
[/TR]
</tbody>[/TABLE]

 

[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