Hi!
I cant seem to make a "short and reliable" worksheet modifier on excel.
Idea is to delete the entire rows if even one of the values corresponds
to N.

Example:
1a Y
1a Y
1a Y
1b Y
1b Y
1b N
1b Y

If there is even one N on the range(1a to 10a) delete all the rows
corresponding to that range.
In this example all rows under 1b would be deleted.
This will be applied to N sheets, so doing it manually(how I started)
results in very messy code and the range might change in the future to
include 11a etc.
Ideas?

A range is generally referred to as A1:A10. It is unclear in your example
what you are referring to. You using N as a value and then using N as the
number of sheets. It is unclear if your values are in a single column or
two or more columns, if 1a is a value in a cell or some designation of a
row.

Row ColA ColB
1 1a Y
2 1a Y
3 1a Y
4 1b Y
5 1b Y
6 1b N
7 1b Y

Might be what your describing, with "1a" (a value in cell A1 for example)
being some code that represents a group and "1b" representing a second
group. Then if any group has a "N" value in column B, then all the rows of
the group should be deleted.

Is that what you are trying to say? If not, then what.

--
Regards,
Tom Ogilvy


"KtM" wrote in message
ps.com...
Hi!
I cant seem to make a "short and reliable" worksheet modifier on excel.
Idea is to delete the entire rows if even one of the values corresponds
to N.

Example:
1a Y
1a Y
1a Y
1b Y
1b Y
1b N
1b Y

If there is even one N on the range(1a to 10a) delete all the rows
corresponding to that range.
In this example all rows under 1b would be deleted.
This will be applied to N sheets, so doing it manually(how I started)
results in very messy code and the range might change in the future to
include 11a etc.
Ideas?

Sorry, should have used a bit more variables and such.

You are correct on the way you put them colA has the items(ranged from
1 to 10 with varying numbers of subcategories(a,b,c,d...) and they are
logically ordered. Actually there is a 3rd column related to this, but
it is not required for this(with the number of degree attained). So,
basically I need to find first N on each subcategory and delete
everything including the line with first N(o) from that subcategory and
forward. In the example deleting lines 6 and 7(and more if theres more
on the 1b).

Sub DeleteRows()
Dim sh As Worksheet
Dim lastrow As Long, i As Long, j As Long
For Each sh In Workhsheets
sh.Activate
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If Cells(i, "B") = "N" Then
j = i
Do While Cells(j, "A") = Cells(i, "A")
j = j + 1
Loop
Range(Cells(i, 1), Cells(j - 1, 1)) _
.EntireRow.Delete
End If
Next i
Next sh
End Sub

--
Regards,
Tom Ogilvy





"KtM" wrote in message
oups.com...
Sorry, should have used a bit more variables and such.

You are correct on the way you put them colA has the items(ranged from
1 to 10 with varying numbers of subcategories(a,b,c,d...) and they are
logically ordered. Actually there is a 3rd column related to this, but
it is not required for this(with the number of degree attained). So,
basically I need to find first N on each subcategory and delete
everything including the line with first N(o) from that subcategory and
forward. In the example deleting lines 6 and 7(and more if theres more
on the 1b).

Thanks, it works nicely after a few modifications(just changed it so it
runs once/each sheet read in)
Btw, if you change the For each sh in worksheets to a counter For Ndx
to X
It produces a nice error I have never seen with XL before :)
(X is some pretermined maximum, like number of sheets etc)

One weird thing is that it only works on certain sheets, but doesnt
touch other sheets read in, all have similar formatting and "same" data
in.

Thank you once again and Happy Holidays.