Select & deleting all rows based on the contents of a cell in the

[Dwoeltje]

I need to create what I thought would be a very simple macro; select some
rows, delete them, and then do a File -> Save As, and save the file out as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in the
spreadsheet contains the column names. The fifth column is called Server
Tested (it is column E). The cells in this column can have only one of four
possible values; www1 or www2 or www3 or www5. What I need to do is to select
all the rows that have a cell, in the 5th column of the row, that has a value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] =
"www5")) That might not be exactly correct syntax, but you get the idea.

But I can't find anything anwhere in the Excel menu structure that will even
let me select rows based specific criteria. I was able to get Excel to select
specific cells based on specific criteria, but it only selected the cell, not
the entire row that the cell was in. And then it did offer me any way to do a
delete after selecting the cells (even if it had, it wouldn't have done me
any good because I needed to delete the whole row, not just that specific
cell).

Can you tell me how to do what I need to do?

 

[Tom Ogilvy]

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

 

[Dwoeltje]

I appreciate the response but I don't think you understood what I described
(or maybe I didn't describe it well enough), so I don't think this is going
to help me. But I appreciate the help.

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy

I need to create what I thought would be a very simple macro; select some
rows, delete them, and then do a File -> Save As, and save the file out as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in the
spreadsheet contains the column names. The fifth column is called Server
Tested (it is column E). The cells in this column can have only one of four
possible values; www1 or www2 or www3 or www5. What I need to do is to select
all the rows that have a cell, in the 5th column of the row, that has a value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] =
"www5")) That might not be exactly correct syntax, but you get the idea.

But I can't find anything anwhere in the Excel menu structure that will even
let me select rows based specific criteria. I was able to get Excel to select
specific cells based on specific criteria, but it only selected the cell, not
the entire row that the cell was in. And then it did offer me any way to do a
delete after selecting the cells (even if it had, it wouldn't have done me
any good because I needed to delete the whole row, not just that specific
cell).

Can you tell me how to do what I need to do?

 

[Dwoeltje]

There must be some way, using VBA source code that I can paste into a macro,
that can search for and select all rows that contain a cell that has a value
of www2 or www3 or www5 and then delete all those rows (leaving only the rows
that have a cell that contains the value www1). Then I can save that macro in
the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel.

I tried using the macro recorder but with no way to select the rows based on
whether a cell was equal to a certain value, all I could do was turn the
"relative" feature on and then manually select all the rows that had cells
containing www2 or www3 or www5. The problem with that is if I add more rows,
that relative setting will be wrong and the macro will need to be redone, so
using the macro recorder that way just doesn't help.

Almost makes me wish I was using Access instead of Excel.

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy

I need to create what I thought would be a very simple macro; select some
rows, delete them, and then do a File -> Save As, and save the file out as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in the
spreadsheet contains the column names. The fifth column is called Server
Tested (it is column E). The cells in this column can have only one of four
possible values; www1 or www2 or www3 or www5. What I need to do is to select
all the rows that have a cell, in the 5th column of the row, that has a value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] =
"www5")) That might not be exactly correct syntax, but you get the idea.

But I can't find anything anwhere in the Excel menu structure that will even
let me select rows based specific criteria. I was able to get Excel to select
specific cells based on specific criteria, but it only selected the cell, not
the entire row that the cell was in. And then it did offer me any way to do a
delete after selecting the cells (even if it had, it wouldn't have done me
any good because I needed to delete the whole row, not just that specific
cell).

Can you tell me how to do what I need to do?

 

[Zone]

Couldn't you just use simple code like
For k=cells(rows.count,"e").end(xlup).row to 2 step-1
if cells(k,"e")<>"www1" then rows(k).entirerow.delete
next k

There must be some way, using VBA source code that I can paste into a
macro,
that can search for and select all rows that contain a cell that has a
value
of www2 or www3 or www5 and then delete all those rows (leaving only the
rows
that have a cell that contains the value www1). Then I can save that macro
in
the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel.

I tried using the macro recorder but with no way to select the rows based
on
whether a cell was equal to a certain value, all I could do was turn the
"relative" feature on and then manually select all the rows that had cells
containing www2 or www3 or www5. The problem with that is if I add more
rows,
that relative setting will be wrong and the macro will need to be redone,
so
using the macro recorder that way just doesn't help.

Almost makes me wish I was using Access instead of Excel.

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except
errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy

I need to create what I thought would be a very simple macro; select
some
rows, delete them, and then do a File -> Save As, and save the file out
as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in
the
spreadsheet contains the column names. The fifth column is called
Server
Tested (it is column E). The cells in this column can have only one of
four
possible values; www1 or www2 or www3 or www5. What I need to do is to
select
all the rows that have a cell, in the 5th column of the row, that has a
value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I
want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2")
or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname]
=
"www5")) That might not be exactly correct syntax, but you get the
idea.

But I can't find anything anwhere in the Excel menu structure that will
even
let me select rows based specific criteria. I was able to get Excel to
select
specific cells based on specific criteria, but it only selected the
cell, not
the entire row that the cell was in. And then it did offer me any way
to do a
delete after selecting the cells (even if it had, it wouldn't have done
me
any good because I needed to delete the whole row, not just that
specific
cell).

Can you tell me how to do what I need to do?

 

[Dwoeltje]

I don't know. Maybe. I'll give it a try.

Couldn't you just use simple code like
For k=cells(rows.count,"e").end(xlup).row to 2 step-1
if cells(k,"e")<>"www1" then rows(k).entirerow.delete
next k

There must be some way, using VBA source code that I can paste into a
macro,
that can search for and select all rows that contain a cell that has a
value
of www2 or www3 or www5 and then delete all those rows (leaving only the
rows
that have a cell that contains the value www1). Then I can save that macro
in
the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel.

I tried using the macro recorder but with no way to select the rows based
on
whether a cell was equal to a certain value, all I could do was turn the
"relative" feature on and then manually select all the rows that had cells
containing www2 or www3 or www5. The problem with that is if I add more
rows,
that relative setting will be wrong and the macro will need to be redone,
so
using the macro recorder that way just doesn't help.

Almost makes me wish I was using Access instead of Excel.

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except
errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy


:

I need to create what I thought would be a very simple macro; select
some
rows, delete them, and then do a File -> Save As, and save the file out
as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in
the
spreadsheet contains the column names. The fifth column is called
Server
Tested (it is column E). The cells in this column can have only one of
four
possible values; www1 or www2 or www3 or www5. What I need to do is to
select
all the rows that have a cell, in the 5th column of the row, that has a
value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I
want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2")
or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname]
=
"www5")) That might not be exactly correct syntax, but you get the
idea.

But I can't find anything anwhere in the Excel menu structure that will
even
let me select rows based specific criteria. I was able to get Excel to
select
specific cells based on specific criteria, but it only selected the
cell, not
the entire row that the cell was in. And then it did offer me any way
to do a
delete after selecting the cells (even if it had, it wouldn't have done
me
any good because I needed to delete the whole row, not just that
specific
cell).

Can you tell me how to do what I need to do?

 

[Dwoeltje]

That worked. Thanks.

Couldn't you just use simple code like
For k=cells(rows.count,"e").end(xlup).row to 2 step-1
if cells(k,"e")<>"www1" then rows(k).entirerow.delete
next k

There must be some way, using VBA source code that I can paste into a
macro,
that can search for and select all rows that contain a cell that has a
value
of www2 or www3 or www5 and then delete all those rows (leaving only the
rows
that have a cell that contains the value www1). Then I can save that macro
in
the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel.

I tried using the macro recorder but with no way to select the rows based
on
whether a cell was equal to a certain value, all I could do was turn the
"relative" feature on and then manually select all the rows that had cells
containing www2 or www3 or www5. The problem with that is if I add more
rows,
that relative setting will be wrong and the macro will need to be redone,
so
using the macro recorder that way just doesn't help.

Almost makes me wish I was using Access instead of Excel.

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except
errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy


:

I need to create what I thought would be a very simple macro; select
some
rows, delete them, and then do a File -> Save As, and save the file out
as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria. The
spreadsheet in question has seven columns it is using. The first row in
the
spreadsheet contains the column names. The fifth column is called
Server
Tested (it is column E). The cells in this column can have only one of
four
possible values; www1 or www2 or www3 or www5. What I need to do is to
select
all the rows that have a cell, in the 5th column of the row, that has a
value
of www2 or www3 or www5 (all rows where that cell has a value of www1 I
want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] = "www2")
or
([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname]
=
"www5")) That might not be exactly correct syntax, but you get the
idea.

But I can't find anything anwhere in the Excel menu structure that will
even
let me select rows based specific criteria. I was able to get Excel to
select
specific cells based on specific criteria, but it only selected the
cell, not
the entire row that the cell was in. And then it did offer me any way
to do a
delete after selecting the cells (even if it had, it wouldn't have done
me
any good because I needed to delete the whole row, not just that
specific
cell).

Can you tell me how to do what I need to do?

 

[Zone]

You're welcome! Thanks for the feedback.

That worked. Thanks.

Couldn't you just use simple code like
For k=cells(rows.count,"e").end(xlup).row to 2 step-1
if cells(k,"e")<>"www1" then rows(k).entirerow.delete
next k

There must be some way, using VBA source code that I can paste into a
macro,
that can search for and select all rows that contain a cell that has a
value
of www2 or www3 or www5 and then delete all those rows (leaving only
the
rows
that have a cell that contains the value www1). Then I can save that
macro
in
the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel.

I tried using the macro recorder but with no way to select the rows
based
on
whether a cell was equal to a certain value, all I could do was turn
the
"relative" feature on and then manually select all the rows that had
cells
containing www2 or www3 or www5. The problem with that is if I add more
rows,
that relative setting will be wrong and the macro will need to be
redone,
so
using the macro recorder that way just doesn't help.

Almost makes me wish I was using Access instead of Excel.

:

in an adjacent column

=if(left(E2,3)="www",if(right(E2,1)*1>1,na(),""),"")
then drag fill down the column

select the column with the formula and do

Edit=>Goto=>Special, select Formulas and uncheck everything except
errors.
Click OK

Do Edit=>Delete and select entire row

delete this column.


It takes longer to describe than it does to do.

--
Regards,
Tom Ogilvy


:

I need to create what I thought would be a very simple macro; select
some
rows, delete them, and then do a File -> Save As, and save the file
out
as a
tab delimited ascii text file. Simple, right? That's what I thought.

Except that I need to select those rows based on certain criteria.
The
spreadsheet in question has seven columns it is using. The first row
in
the
spreadsheet contains the column names. The fifth column is called
Server
Tested (it is column E). The cells in this column can have only one
of
four
possible values; www1 or www2 or www3 or www5. What I need to do is
to
select
all the rows that have a cell, in the 5th column of the row, that
has a
value
of www2 or www3 or www5 (all rows where that cell has a value of
www1 I
want
to be left alone), and then delete those rows.

Now, if this were a database and I was using SQL, I might use
something
similar to this:

Delete * from tablename (where ([dbname.tablename.fieldname] =
"www2")
or
([dbname.tablename.fieldname] = "www3" or
([dbname.tablename.fieldname]
=
"www5")) That might not be exactly correct syntax, but you get the
idea.

But I can't find anything anwhere in the Excel menu structure that
will
even
let me select rows based specific criteria. I was able to get Excel
to
select
specific cells based on specific criteria, but it only selected the
cell, not
the entire row that the cell was in. And then it did offer me any
way
to do a
delete after selecting the cells (even if it had, it wouldn't have
done
me
any good because I needed to delete the whole row, not just that
specific
cell).

Can you tell me how to do what I need to do?