Removing every other line

  • Thread starter Thread starter Sawyer
  • Start date Start date
S

Sawyer

Hello All
I need a script to remove every other line in a excel document, starting at
say line 10 and delete line 11,13,15 and so on is this something that can be
done.
Thanks for any help
Scott
 
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Ok I think that just when a little over my head, what do you do with the A1:
MyCriteria A2: =ISEVEN(ROW(A10)) where does this part go I think I got the
rest of it ok.

Thanks



Ron Coderre said:
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Hello All
I need a script to remove every other line in a excel document, starting
at
say line 10 and delete line 11,13,15 and so on is this something that can
be
done.
Thanks for any help
Scott
Click to expand...
Click to expand...
 
Another way, insert a new column A by selecting column A and do
insert>columns, then in A10 put 1, select A10 and A11, right click the lower
right corner of A11 and drag down as long as you want, when you release the
right mouse button select copy cells. That would give you 1 in A10, blank in
A11, 1 in A12, blank in A13 and so on, now select the range with 1s and
blanks that you have created, press F5, click special and select blanks,
click OK. Now do edit>delete and when prompted select entire row. Finish by
deleting column A

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




Sawyer said:
Ok I think that just when a little over my head, what do you do with the
A1: MyCriteria A2: =ISEVEN(ROW(A10)) where does this part go I think I got
the rest of it ok.

Thanks



Ron Coderre said:
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a
filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Hello All
I need a script to remove every other line in a excel document, starting
at
say line 10 and delete line 11,13,15 and so on is this something that
can be
done.
Thanks for any help
Scott
Click to expand...
Click to expand...
Click to expand...
 
The method exploits a feature of Advanced Filters and eliminates the need to
create helper columns and copy formulas through hundreds, or thousands, of
cells.

Here's the way the criteria section works:
A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Since we are using an formulaic criteria, we can't use column heading that
matches anything in the data list. Hence: MyCriteria

The =ISEVEN(ROW(A10)) formula is located in cell A2, but refers to the first
row of data. When the Advanced Filter runs it sequentially applies that
formula to each cell in column a, beginning in A10.....then it internally
applies it to A11, A12, etc.
For A10 the formula returns TRUE (so the row is shown).
For A11 the formula returns FALSE (so the row is hidden).
etc....to the end of the data list

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Ok I think that just when a little over my head, what do you do with the A1:
MyCriteria A2: =ISEVEN(ROW(A10)) where does this part go I think I got the
rest of it ok.

Thanks



Ron Coderre said:
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Hello All
I need a script to remove every other line in a excel document, starting
at
say line 10 and delete line 11,13,15 and so on is this something that can
be
done.
Thanks for any help
Scott
Click to expand...
Click to expand...
Click to expand...
 
That filter stuff is neat. I need to find out more about it.
What I would have done is write a macro to start at the bottom, and worked
up, deleting and shifting rows.

Ron Coderre said:
The method exploits a feature of Advanced Filters and eliminates the need to
create helper columns and copy formulas through hundreds, or thousands, of
cells.

Here's the way the criteria section works:
A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Since we are using an formulaic criteria, we can't use column heading that
matches anything in the data list. Hence: MyCriteria

The =ISEVEN(ROW(A10)) formula is located in cell A2, but refers to the first
row of data. When the Advanced Filter runs it sequentially applies that
formula to each cell in column a, beginning in A10.....then it internally
applies it to A11, A12, etc.
For A10 the formula returns TRUE (so the row is shown).
For A11 the formula returns FALSE (so the row is hidden).
etc....to the end of the data list

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Ok I think that just when a little over my head, what do you do with the A1:
MyCriteria A2: =ISEVEN(ROW(A10)) where does this part go I think I got the
rest of it ok.

Thanks



Ron Coderre said:
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hello All
I need a script to remove every other line in a excel document, starting
at
say line 10 and delete line 11,13,15 and so on is this something that can
be
done.
Thanks for any help
Scott
Click to expand...
Click to expand...
Click to expand...
 
That filter stuff is great. Just what I had been looking for just this
morning, to temporarily remove a bunch of rows that contain blanks in a
certain column. Now I can get a lot more of the rows I'm looking at on the
screen and don't have to be paging down constantly! You guys are wonderful!

Ron Coderre said:
The method exploits a feature of Advanced Filters and eliminates the need to
create helper columns and copy formulas through hundreds, or thousands, of
cells.

Here's the way the criteria section works:
A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Since we are using an formulaic criteria, we can't use column heading that
matches anything in the data list. Hence: MyCriteria

The =ISEVEN(ROW(A10)) formula is located in cell A2, but refers to the first
row of data. When the Advanced Filter runs it sequentially applies that
formula to each cell in column a, beginning in A10.....then it internally
applies it to A11, A12, etc.
For A10 the formula returns TRUE (so the row is shown).
For A11 the formula returns FALSE (so the row is hidden).
etc....to the end of the data list

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Sawyer said:
Ok I think that just when a little over my head, what do you do with the A1:
MyCriteria A2: =ISEVEN(ROW(A10)) where does this part go I think I got the
rest of it ok.

Thanks



Ron Coderre said:
Try this:

Just one rule:
Cells A9 through A11 must contain any kind of value

A1: MyCriteria
A2: =ISEVEN(ROW(A10))

Data|Filter|Advanced Filter
List Range: (select from A9 down as far as you need)
Criteria Range: (Select A1:A2)
Click the [OK] button

The filter will only display even numbered rows under A9
Select the filtered rows under A9

Edit|Delete (Excel will only allow you to delete entire rows when a filter
is engaged)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hello All
I need a script to remove every other line in a excel document, starting
at
say line 10 and delete line 11,13,15 and so on is this something that can
be
done.
Thanks for any help
Scott
Click to expand...
Click to expand...
Click to expand...
 
Back
Top