deleting cells in a range
 

hi
i need to step through the range A3:J107 deleting (move up) cells meeting a
certain criteria (value 10) ... and i just can't seem to get the code
right, please help

Cheers
JulieD

deleting cells in a range
 

You don't care about row integrity?

Sub BB()
Set rng = Range("A3:J107")
last = rng.Count
For i = last To 1 Step -1
If rng(i).Value 10 Then
rng(i).Delete Shift:=xlShiftUp
End If
Next
End Sub

seems to work.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
hi
i need to step through the range A3:J107 deleting (move up) cells meeting
a
certain criteria (value 10) ... and i just can't seem to get the code
right, please help

Cheers
JulieD

deleting cells in a range
 

Hi Tom

You don't care about row integrity?
not in this case, separate tables on a worksheet

thanks for the code, it worked well!


Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You don't care about row integrity?

Sub BB()
Set rng = Range("A3:J107")
last = rng.Count
For i = last To 1 Step -1
If rng(i).Value 10 Then
rng(i).Delete Shift:=xlShiftUp
End If
Next
End Sub

seems to work.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
hi
i need to step through the range A3:J107 deleting (move up) cells meeting
a
certain criteria (value 10) ... and i just can't seem to get the code
right, please help

Cheers
JulieD

deleting cells in a range
 

That was a very interesting routine...and very useful in manipulating data.
Can you show what it would look like if one DID want row integrity? Seems to
me there would be no change in the filtered area if any cell on a row had
data in it in that scenario.

John
La Mesa, CA

"Tom Ogilvy" wrote in message
...
You don't care about row integrity?

Sub BB()
Set rng = Range("A3:J107")
last = rng.Count
For i = last To 1 Step -1
If rng(i).Value 10 Then
rng(i).Delete Shift:=xlShiftUp
End If
Next
End Sub

seems to work.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
hi
i need to step through the range A3:J107 deleting (move up) cells meeting
a
certain criteria (value 10) ... and i just can't seem to get the code
right, please help

Cheers
JulieD

deleting cells in a range
 

If you wanted row integrity, you would delete the entirerow based on the
condition - perhaps if any cell had a value greater than 10 you would delete
the entire row.

for i =107 to 3 step -1
for j = 1 to 10
if cells(i,j)10 then
rows(i).delete
exit for
end if
Next
Next

or
for i = 107 to 3 step -1
if application.countif(cells(i,1).Resize(1,10),"10") 0 then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy

"John W Cavoulas" wrote in message
news:Hep0e.175866$FM3.144882@fed1read02...
That was a very interesting routine...and very useful in manipulating
data.
Can you show what it would look like if one DID want row integrity? Seems
to
me there would be no change in the filtered area if any cell on a row had
data in it in that scenario.

John
La Mesa, CA

"Tom Ogilvy" wrote in message
...
You don't care about row integrity?

Sub BB()
Set rng = Range("A3:J107")
last = rng.Count
For i = last To 1 Step -1
If rng(i).Value 10 Then
rng(i).Delete Shift:=xlShiftUp
End If
Next
End Sub

seems to work.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
hi
i need to step through the range A3:J107 deleting (move up) cells
meeting
a
certain criteria (value 10) ... and i just can't seem to get the code
right, please help

Cheers
JulieD

deleting cells in a range
 

Thanks Tom. I was curious about that. It will be a helpful tool for deleting
incomplete records (rows of data). Our testers aren't perfect. ;-)

John


"Tom Ogilvy" wrote in message
...
If you wanted row integrity, you would delete the entirerow based on the
condition - perhaps if any cell had a value greater than 10 you would
delete
the entire row.

for i =107 to 3 step -1
for j = 1 to 10
if cells(i,j)10 then
rows(i).delete
exit for
end if
Next
Next

or
for i = 107 to 3 step -1
if application.countif(cells(i,1).Resize(1,10),"10") 0 then
rows(i).Delete
end if
Next

--
Regards,
Tom Ogilvy

"John W Cavoulas" wrote in message
news:Hep0e.175866$FM3.144882@fed1read02...
That was a very interesting routine...and very useful in manipulating
data.
Can you show what it would look like if one DID want row integrity? Seems
to
me there would be no change in the filtered area if any cell on a row had
data in it in that scenario.

John
La Mesa, CA

"Tom Ogilvy" wrote in message
...
You don't care about row integrity?

Sub BB()
Set rng = Range("A3:J107")
last = rng.Count
For i = last To 1 Step -1
If rng(i).Value 10 Then
rng(i).Delete Shift:=xlShiftUp
End If
Next
End Sub

seems to work.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
hi
i need to step through the range A3:J107 deleting (move up) cells
meeting
a
certain criteria (value 10) ... and i just can't seem to get the
code
right, please help

Cheers
JulieD