use vb to delete marked rows
 

Having trouble with this one, hope someone can help me with the code.
I have a sheet with rows 3-700 and columns A-J. I want to have some vb code
that when run(doing it on a click action for a button) deletes rows that I
have marked with an X in cell J3-J700. So if it sees a X in cell J3, it
highlights the entire row and deletes it, then move on to J4 and checks for
the X and so on. If there is no X then it should just move to the next one
and keep checking.
I can get it to sort of work with if/then statements, but one problem I'm
having is when you delete a row they all shift up, so just going to the next
row starts to "skip" rows that were moved up. And the other problem is how
to simplify the code for J3-J700 so I don't have to put 700 if/then
statements in. Thanks for any help.

use vb to delete marked rows
 

Why not use dataautofilterfilter on x and delete all rows. Record a macro
if desired


--
Don Guillett
SalesAid Software

"Phil" wrote in message
...
Having trouble with this one, hope someone can help me with the code.
I have a sheet with rows 3-700 and columns A-J. I want to have some vb
code
that when run(doing it on a click action for a button) deletes rows that I
have marked with an X in cell J3-J700. So if it sees a X in cell J3, it
highlights the entire row and deletes it, then move on to J4 and checks
for
the X and so on. If there is no X then it should just move to the next one
and keep checking.
I can get it to sort of work with if/then statements, but one problem I'm
having is when you delete a row they all shift up, so just going to the
next
row starts to "skip" rows that were moved up. And the other problem is how
to simplify the code for J3-J700 so I don't have to put 700 if/then
statements in. Thanks for any help.

use vb to delete marked rows
 

I never used the autofilter so going thru the help files now. One question I
have is in the cells in column I we have numbers that get totaled at the
end. If I use autofilter to hides all the rows marked with X will my total
still add all the values from I3:I700 including the hidden ones? I don't
want it to. When I mark it with an X and it hides I need those values in
cells I3:I700 to not add into the total. That's why I thought just deleting
those rows was the way to go. The total formula is: =sum(I3:I700)
Basicly I'm saying I want to mark rows that I don't want to add into the
total. However that is accomplished doesn't matter either deleting or hiding
the rows. Thanks.

Don Guillett wrote:
Why not use dataautofilterfilter on x and delete all rows. Record a
macro if desired


Having trouble with this one, hope someone can help me with the code.
I have a sheet with rows 3-700 and columns A-J. I want to have some
vb code that when run(doing it on a click action for a button)
deletes rows that I have marked with an X in cell J3-J700. So if it
sees a X in cell J3, it highlights the entire row and deletes it,
then move on to J4 and checks for the X and so on. If there is no X
then it should just move to the next one and keep checking.
I can get it to sort of work with if/then statements, but one
problem I'm having is when you delete a row they all shift up, so
just going to the next row starts to "skip" rows that were moved up.
And the other problem is how to simplify the code for J3-J700 so I
don't have to put 700 if/then statements in. Thanks for any help.

use vb to delete marked rows
 

How about the sumif function
=SUMIF(J3:J700,"<X",I3:I700)

If you really want to delete the rows, use a for next loop and start at the
bottom and work up.
Paul D

"Phil" wrote in message
...
I never used the autofilter so going thru the help files now. One question
I
have is in the cells in column I we have numbers that get totaled at the
end. If I use autofilter to hides all the rows marked with X will my total
still add all the values from I3:I700 including the hidden ones? I don't
want it to. When I mark it with an X and it hides I need those values in
cells I3:I700 to not add into the total. That's why I thought just
deleting
those rows was the way to go. The total formula is: =sum(I3:I700)
Basicly I'm saying I want to mark rows that I don't want to add into the
total. However that is accomplished doesn't matter either deleting or
hiding
the rows. Thanks.

Don Guillett wrote:
Why not use dataautofilterfilter on x and delete all rows. Record a
macro if desired


Having trouble with this one, hope someone can help me with the code.
I have a sheet with rows 3-700 and columns A-J. I want to have some
vb code that when run(doing it on a click action for a button)
deletes rows that I have marked with an X in cell J3-J700. So if it
sees a X in cell J3, it highlights the entire row and deletes it,
then move on to J4 and checks for the X and so on. If there is no X
then it should just move to the next one and keep checking.
I can get it to sort of work with if/then statements, but one
problem I'm having is when you delete a row they all shift up, so
just going to the next row starts to "skip" rows that were moved up.
And the other problem is how to simplify the code for J3-J700 so I
don't have to put 700 if/then statements in. Thanks for any help.

use vb to delete marked rows
 

Great. I never thought about it like that, work with the sum. Also didn't
think of working from the bottom up. I'm a vb novice, but am familar with
the for next. Thanks alot, I'll do some playing around with. Thanks.

Paul D wrote:
How about the sumif function
=SUMIF(J3:J700,"<X",I3:I700)

If you really want to delete the rows, use a for next loop and start
at the bottom and work up.
Paul D

"Phil" wrote in message
...
I never used the autofilter so going thru the help files now. One
question I have is in the cells in column I we have numbers that get
totaled at the end. If I use autofilter to hides all the rows marked
with X will my total still add all the values from I3:I700 including
the hidden ones? I don't want it to. When I mark it with an X and it
hides I need those values in cells I3:I700 to not add into the
total. That's why I thought just deleting those rows was the way to
go. The total formula is: =sum(I3:I700) Basicly I'm saying I want
to mark rows that I don't want to add into the total. However that
is accomplished doesn't matter either deleting or hiding the rows.
Thanks.

Don Guillett wrote:
Why not use dataautofilterfilter on x and delete all rows. Record
a macro if desired


Having trouble with this one, hope someone can help me with the
code. I have a sheet with rows 3-700 and columns A-J. I want to
have some vb code that when run(doing it on a click action for a
button) deletes rows that I have marked with an X in cell J3-J700.
So if it sees a X in cell J3, it highlights the entire row and
deletes it, then move on to J4 and checks for the X and so on. If
there is no X then it should just move to the next one and keep
checking.
I can get it to sort of work with if/then statements, but one
problem I'm having is when you delete a row they all shift up, so
just going to the next row starts to "skip" rows that were moved
up. And the other problem is how to simplify the code for J3-J700
so I don't have to put 700 if/then statements in. Thanks for any
help.

use vb to delete marked rows
 

You can use the autofilter to only show the X rows. Then delete all and it
will only delete the visible. OR, if you dont' want to delete, use SUBTOTAL
to get the total or just leave alone entirely and use SUMIF or SUMPRODUCT to
the info you want.

--
Don Guillett
SalesAid Software

"Phil" wrote in message
...
I never used the autofilter so going thru the help files now. One question
I
have is in the cells in column I we have numbers that get totaled at the
end. If I use autofilter to hides all the rows marked with X will my total
still add all the values from I3:I700 including the hidden ones? I don't
want it to. When I mark it with an X and it hides I need those values in
cells I3:I700 to not add into the total. That's why I thought just
deleting
those rows was the way to go. The total formula is: =sum(I3:I700)
Basicly I'm saying I want to mark rows that I don't want to add into the
total. However that is accomplished doesn't matter either deleting or
hiding
the rows. Thanks.

Don Guillett wrote:
Why not use dataautofilterfilter on x and delete all rows. Record a
macro if desired


Having trouble with this one, hope someone can help me with the code.
I have a sheet with rows 3-700 and columns A-J. I want to have some
vb code that when run(doing it on a click action for a button)
deletes rows that I have marked with an X in cell J3-J700. So if it
sees a X in cell J3, it highlights the entire row and deletes it,
then move on to J4 and checks for the X and so on. If there is no X
then it should just move to the next one and keep checking.
I can get it to sort of work with if/then statements, but one
problem I'm having is when you delete a row they all shift up, so
just going to the next row starts to "skip" rows that were moved up.
And the other problem is how to simplify the code for J3-J700 so I
don't have to put 700 if/then statements in. Thanks for any help.