Hello all,
Is it possible to creata an AUTO_OPEN such that all rows in the
spread-sheet which satisfy a certain criterir get deleted ? The data is
obviously dynamic. When I used the AUTO_OPEN and created a macro which
deleted the rows (I was manually doing the process and recording the
macro), if the data did not change, it worked fine. If I changed the
data, as in added or removed rows that fulfilled the criteria for
deletion, the thing went haywire - I realized - the deletion was
working only on a range and if the data changed, the range changed, and
this got messed up.


here is the criteria for deletion

Column A can be 2 values - Error or Valid
Coumn B can be multiple values for a country...say US, CA, UK...
Column C can be multiple values for a type...Hardware, Software,
Warranty...

I have set up an auto-filter using the AUTO_OPEN macro for

ErrorIND, COUNTRY, TYPE etc...

So my criteria for deletion is (using values from auto-filter)

ErrorIND = Error
Country = CA
Type = Software

All such rows should be deleted by the macro and nothing else..

Thanks.

just adjust your current macro. Assuming no completely blank rows mixed in
with your data

worksheets("sheet1").Range("A1").CurrentRegion.Aut ofilter . . .

If you have blank rows

With Worksheets("sheet1")
set rng =.range(.cells(1,1),.cells(rows.count,1).end(xlup) )
End with
set rng = rng.Resize(,20) ' change 20 to the number of columns
rng.Autofilter . . .

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"Raj." wrote:

Hello all,
Is it possible to creata an AUTO_OPEN such that all rows in the
spread-sheet which satisfy a certain criterir get deleted ? The data is
obviously dynamic. When I used the AUTO_OPEN and created a macro which
deleted the rows (I was manually doing the process and recording the
macro), if the data did not change, it worked fine. If I changed the
data, as in added or removed rows that fulfilled the criteria for
deletion, the thing went haywire - I realized - the deletion was
working only on a range and if the data changed, the range changed, and
this got messed up.


here is the criteria for deletion

Column A can be 2 values - Error or Valid
Coumn B can be multiple values for a country...say US, CA, UK...
Column C can be multiple values for a type...Hardware, Software,
Warranty...

I have set up an auto-filter using the AUTO_OPEN macro for

ErrorIND, COUNTRY, TYPE etc...

So my criteria for deletion is (using values from auto-filter)

ErrorIND = Error
Country = CA
Type = Software

All such rows should be deleted by the macro and nothing else..

Thanks.