Delete rows by content

[Guest]

I am trying to automate many of the task that I do with excel. One of those
tasks it to sort a database. I've made a macro to do most of the work for me
but one aspect has stumped me.

I want to delete rows in the spreadsheet by what their memeber number is.
Example:
Column A contains the members numbers.
members from gym X are as follows: X0001, X0002, X0003, etc.
members from gym Y are as follows: Y0001, Y0002, Y0003, etc.
members from gym Z are as follows: Z0001, Z0002, Z0003, etc. (you get the
idea)

I want to delete any members from gym X and Z (or any other crazy number in
the database) but keep my members for gym Y.
The amount of members is always changing. Any suggestions?

 

[RB-MC]

Why not simply filter the database for the gym members you want to
delete, delete the filtered rows and undo the filter?

HTH

I am trying to automate many of the task that I do with excel. One of those
tasks it to sort a database. I've made a macro to do most of the work for me
but one aspect has stumped me.

I want to delete rows in the spreadsheet by what their memeber number is.
Example:
Column A contains the members numbers.
members from gym X are as follows: X0001, X0002, X0003, etc.
members from gym Y are as follows: Y0001, Y0002, Y0003, etc.
members from gym Z are as follows: Z0001, Z0002, Z0003, etc. (you get the
idea)

I want to delete any members from gym X and Z (or any other crazy number in
the database) but keep my members for gym Y.
The amount of members is always changing. Any suggestions?

RB
__

 

[Guest]

Dim sourcerow
sourcerow = 1
While Cells(sourcerow, 1) <> ""
if left(cells(sourcerow,1))<>"Y" then
rows(sourcerow & ":" & sourcerow).delete
else
sourcerow = sourcerow + 1
end if
Wend
This just continues until it finds column A blank in the next sourcerow.
While not blank, it checks for members not like Y* and deletes such rows. If
this is a long list, you may want to turn screenupdating off before the loop
and back on after (application.screenupdating = false,
application.screenupdating = true).
--Bruce

 

[Dave Peterson]

And just to add to RB-MC's reply:

You can choose a custom filter of "begins with" to show the X##### cells.

Then you can delete the visible rows and do it again.