use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{ReferenceStyle} = 1;    #xlA1. xlR1C1 = -4150?!?!
$xl->{ScreenUpdating} = 0;    #ScreenUpdating is false because the use
+r would not normally need to see the Excel instance churning
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $nSheets = $wb->Sheets->Count; #I want 2 sheets - no more, no less.
+ No matter what $luser's default is.
if ($nSheets == 1) {
    $wb->Sheets->Add({After=>$wb->Sheets(1)});
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtData = $wb->Sheets(1);
my $shtCri  = $wb->Sheets(2);
$shtData->{Name} = "Data";
$shtCri->{Name}  = "Criteria";

for my $sht (1..2) {
    $wb->Sheets($sht)->Cells(1, 1)->{Value} = "A";
    $wb->Sheets($sht)->Cells(1, 2)->{Value} = "B";
    $wb->Sheets($sht)->Cells(2, 1)->{Value} = "-";
    $wb->Sheets($sht)->Cells(2, 2)->{Value} = "-";
    $wb->Sheets($sht)->Range("A2:B2")->{HorizontalAlignment} = 5 #xlFi
+ll
}

$wb->Names->Add({Name=>'zTopData', RefersTo=>'=Data!$A$1'});
$wb->Names->Add({Name=>'zEndData', RefersTo=>'=Data!$B$2'});
$wb->Names->Add({Name=>'zData', RefersTo=>'=OFFSET(zTopData,0,0,ROW(zE
+ndData)-ROW(zTopData),COLUMN(zEndData)-COLUMN(zTopData)+1)'});
$wb->Names->Add({Name=>'zTopCriterion', RefersTo=>'=Criteria!$A$1'});
$wb->Names->Add({Name=>'zEndCriterion', RefersTo=>'=Criteria!$B$2'});
$wb->Names->Add({Name=>'zCriterion', RefersTo=>'=OFFSET(zTopCriterion,
+0,0,ROW(zEndCriterion)-ROW(zTopCriterion),COLUMN(zEndCriterion)-COLUM
+N(zTopCriterion)+1)'});

my $nRows = 98; #Your example has 99 rows in the rnge. This smells lik
+e a deliberate overrun to me, but just in case...
for (1 .. $nRows) { #Insert some random data
    $shtData->Range('zEndData')->EntireRow->Insert;
    $shtData->Cells($_ + 1, 1)->{Value} = int(rand(10)+1);
    $shtData->Cells($_ + 1, 2)->{Value} = int(rand(10)+1);
}

$shtCri->Range('zEndCriterion')->EntireRow->Insert;
$shtCri->Cells(2, 1)->{Value} = int(rand(10)+1);
$shtData->Range('zData')->AdvancedFilter({Action=>1, CriteriaRange=>$s
+htCri->Range('zCriterion')}); #xlFilterInPlace = 1
$shtData->Activate; #To get to the filtered list

$xl->{EnableEvents}   = 1;
$xl->{ScreenUpdating} = 1;
[download]

I feared that the "with" clause was doing what you described. I don't know about other languages, but while it will compile in VB6, it won't execute twice - the second call causes a weird crash. This is documented, but I didn't find the documentation before suffering the wounds. I'm therefore very reluctant to use With except in VBA, where it works.

The code I have written includes no "drop down list". This is a point on which I am unclear what you want. You could have a combobox - the sort of control you might put on a form or on the face of the spreadsheet - or you could use Data Validation of a cell. The techniques are quite different when it comes to populating the list. But since I don't know how you want the list populated anyway, I've left it out.

The two techniques I have used are advanced filter and dynamic ranges. Provided users insert rows between the header and the dotted lines, the data will be handled correctly. But read up on advanced filters. Two criteria on the same line are treated as "and", while on separate lines they are "or". A blank cell matches everything, therefore a blank line will mean that nothing is filtered out. Changing the criteria will not automatically re-work the filter. In 123, this could be done by hitting {F7}, but Bill Gates knows you don't want or need this. Instead, you have to write a macro using Worksheet_Change to do all the work.

Regards,

John Davies