Conditional Delete in Excel using Macro

[rhjaisingh]

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. Any help ?
Thanks much.

 

[Ikaabod]

The reason is because you only recorded a macro. So it wants to delete
the exact same rows as you deleted when recording. If you post your
"criteria" to determine if a row is to be deleted, it might be easier
for someone to help you out. To answer your first question though, it
is possible.

rhjaisingh said:
<
<Conditional Delete in Excel using Macro
<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. Any help ?
<Thanks much.

 

[rhjaisingh]

OK, here is the criteria -

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.

 

[rhjaisingh]

OK, here is the criteria -

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.

 

[Ikaabod]

Would something like this work for you?

Private Sub Workbook_Open()
Dim mySheet As String
Dim myErrorIND As String
Dim myCountry As String
Dim myType As String

mySheet = "Sheet1"
myErrorIND = "Error"
myCountry = "CA"
myType = "Software"

Worksheets(mySheet).Activate
Range("A1").Select
Do
If ActiveCell = myErrorIND And ActiveCell.Offset(0, 1) = myCountry And
ActiveCell.Offset(0, 2) = myType Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop Until IsEmpty(ActiveCell)
Range("A1").Select
End Sub

 

[Raj.]

Hello,

Thanks for writing back. I wish I knew coding using VBA, but what I
have is COBOL and some C. Let me tell you what I have -

The filename is e2edata.xls - The sheet in it is called dv05Output.
The first row is a header which has column names viz -
Product, Version, ErrorInd (Coumn C -1 of the criteria), Some Col, Some
Col, Some Col, Country (Coumn G - 1 of the criteria for deletion), Some
Col, Category (Column I - 1 of the criteria for deletion). The actual
values for data start after the header row, so they start in row 2. By
using the record macro button, I have created a macro that I undestand
by looking, but couldn't have done it myself.

This macro is called Auto_Open which is executed when the spread-sheet
is opened thru a batch process (.cmd or .bat). the macro does it's
thing and closes the .xls for me. Problem is the deletion - it would
not delete the said rows - so I have been doing it manually - I am
going to try your code - but I doubt it will work since commands like
Range("A1").Select may be different for me - Does this mean start from
the first cell - my ErrorInd is column C, the first valuewould be in
Cell C2 - next would be G2 and I2 -

I will try and run thru the macro you created - maybe I can figure it
out - but would appreciate if you would tell me how exactly I would
code this -

Thx.