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
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
"Sawyer" wrote:
> 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
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" wrote:
>>
>>> 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
>>>
>>>
>>>
>
>
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" wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>
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" wrote:
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >>
> > >>
> >
> >
> >
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" wrote:
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >>
> > >>
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks