Conditional delete using saved macro possible ?

[Raj.]

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.

[Tom Ogilvy]

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

worksheets("sheet1").Range("A1").CurrentRegion.Autofilter . . .

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.
>
>