I am trying to run a VBA For/ Next loop that contains an If function
e.g. For n = 1 to 600, If cell C1 ="XYZ" then Range("A1:J1") Select
Selection delete ,End If next n. I want to be able to identify th
Cell and the Range using n. i.e. I want to identify the cell as Cn an
the Range as "An:Jn" . How do I do that

--
knowtrum
-----------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...fo&userid=1966
View this thread: http://www.excelforum.com/showthread.php?threadid=50266

First, it'll be easier to start at the bottom and work your way up. Try it top
down and you'll see the problem:

dim n as long
with activesheet
for n = 600 to 1 step -1
if lcase(.cells(n,"C").value) = "xyz" then
'delete entire row, then use the .rows(n).delete
.rows(n).delete
'or
.cells(n,"A").resize(1,10).delete shift:=xlup
next n
end with

you could also use:

.range(.cells(n,"A"),.cells(n,"J")).delete shift:=xlup
or
.range("A" & n & ":J" & n).delete shift:=xlup




knowtrump wrote:

I am trying to run a VBA For/ Next loop that contains an If function,
e.g. For n = 1 to 600, If cell C1 ="XYZ" then Range("A1:J1") Select,
Selection delete ,End If next n. I want to be able to identify the
Cell and the Range using n. i.e. I want to identify the cell as Cn and
the Range as "An:Jn" . How do I do that?

--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=502664

--

Dave Peterson

Thanks Dave, the first solution worked perfectly.


--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=502664

Be careful. You only wanted to keep one of these lines:

.rows(n).delete
'or
.cells(n,"A").resize(1,10).delete shift:=xlup

If you kept both, your data was probably damaged.

knowtrump wrote:

Thanks Dave, the first solution worked perfectly.

--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=502664

--

Dave Peterson