I'd like to delete rows from a spreadsheet based on the value of a cell in a
row. This is a very large spreadsheet (60,000+ lines). I want to delete
rows based on a value (in this case all parts which are purchased- vs mfd)
without destroying the spreadsheet structure. In this case I have several
bills of material where I want to list all the assemblies, but not the
purchased parts. It seems it should be possible, but I'm not having any luck
with the filter method.

Thanks,

Dave

sort??
or use a macro to delete, from the bottom up, if the cell contains your text


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Dave" <(E-Mail Removed)> wrote in message
news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
> I'd like to delete rows from a spreadsheet based on the value of a cell in
> a
> row. This is a very large spreadsheet (60,000+ lines). I want to delete
> rows based on a value (in this case all parts which are purchased- vs mfd)
> without destroying the spreadsheet structure. In this case I have several
> bills of material where I want to list all the assemblies, but not the
> purchased parts. It seems it should be possible, but I'm not having any
> luck
> with the filter method.
>
> Thanks,
>
> Dave

I don't want to sort, because I'd lose the BOM structure. I just want to
remove the purchased items from the lists so I have the manufactured part
numbers left in the BOM structure.

How would you create a macro to do that?

"Don Guillett" wrote:

> sort??
> or use a macro to delete, from the bottom up, if the cell contains your text
>
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Dave" <(E-Mail Removed)> wrote in message
> news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
> > I'd like to delete rows from a spreadsheet based on the value of a cell in
> > a
> > row. This is a very large spreadsheet (60,000+ lines). I want to delete
> > rows based on a value (in this case all parts which are purchased- vs mfd)
> > without destroying the spreadsheet structure. In this case I have several
> > bills of material where I want to list all the assemblies, but not the
> > purchased parts. It seems it should be possible, but I'm not having any
> > luck
> > with the filter method.
> >
> > Thanks,
> >
> > Dave
>
>
>

Dave,

Have you tried using the autofilter for the purchased field?

Beege

"Dave" <(E-Mail Removed)> wrote in message
news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
> I'd like to delete rows from a spreadsheet based on the value of a cell in
> a
> row. This is a very large spreadsheet (60,000+ lines). I want to delete
> rows based on a value (in this case all parts which are purchased- vs mfd)
> without destroying the spreadsheet structure. In this case I have several
> bills of material where I want to list all the assemblies, but not the
> purchased parts. It seems it should be possible, but I'm not having any
> luck
> with the filter method.
>
> Thanks,
>
> Dave

Beege,

Yeah, I tried the autofilter, but maybe (probably) I'm not familiar enough
with it. When I filter, how can I specify I just want to see the mfd parts
without destroying the BOM structure?

"Beege" wrote:

> Dave,
>
> Have you tried using the autofilter for the purchased field?
>
> Beege
>
> "Dave" <(E-Mail Removed)> wrote in message
> news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
> > I'd like to delete rows from a spreadsheet based on the value of a cell in
> > a
> > row. This is a very large spreadsheet (60,000+ lines). I want to delete
> > rows based on a value (in this case all parts which are purchased- vs mfd)
> > without destroying the spreadsheet structure. In this case I have several
> > bills of material where I want to list all the assemblies, but not the
> > purchased parts. It seems it should be possible, but I'm not having any
> > luck
> > with the filter method.
> >
> > Thanks,
> >
> > Dave
>
>
>

You CAN sort by creating a column with numbers>sort to delete desired>resort
based on numbers

for i=cells(rows.count,1).end(xlup).row to 2 step -1
if cells(i,"c")="yourtext" then rows(i).delete
next i

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Dave" <(E-Mail Removed)> wrote in message
news:2F77A2DB-1A63-4E0B-A681-(E-Mail Removed)...
>I don't want to sort, because I'd lose the BOM structure. I just want to
> remove the purchased items from the lists so I have the manufactured part
> numbers left in the BOM structure.
>
> How would you create a macro to do that?
>
> "Don Guillett" wrote:
>
>> sort??
>> or use a macro to delete, from the bottom up, if the cell contains your
>> text
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Dave" <(E-Mail Removed)> wrote in message
>> news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
>> > I'd like to delete rows from a spreadsheet based on the value of a cell
>> > in
>> > a
>> > row. This is a very large spreadsheet (60,000+ lines). I want to
>> > delete
>> > rows based on a value (in this case all parts which are purchased- vs
>> > mfd)
>> > without destroying the spreadsheet structure. In this case I have
>> > several
>> > bills of material where I want to list all the assemblies, but not the
>> > purchased parts. It seems it should be possible, but I'm not having
>> > any
>> > luck
>> > with the filter method.
>> >
>> > Thanks,
>> >
>> > Dave
>>
>>
>>

Dave
Is there a separate column for the make/buy indicator? You'd autofilter on
that one. Select the column, Data/Filter Autofilter. Or you could try
messing around with advanced filter, but I don't use that much

Beege

"Dave" <(E-Mail Removed)> wrote in message
news:497B0DBA-8D82-420B-9784-(E-Mail Removed)...
> Beege,
>
> Yeah, I tried the autofilter, but maybe (probably) I'm not familiar enough
> with it. When I filter, how can I specify I just want to see the mfd
> parts
> without destroying the BOM structure?
>
> "Beege" wrote:
>
>> Dave,
>>
>> Have you tried using the autofilter for the purchased field?
>>
>> Beege
>>
>> "Dave" <(E-Mail Removed)> wrote in message
>> news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
>> > I'd like to delete rows from a spreadsheet based on the value of a cell
>> > in
>> > a
>> > row. This is a very large spreadsheet (60,000+ lines). I want to
>> > delete
>> > rows based on a value (in this case all parts which are purchased- vs
>> > mfd)
>> > without destroying the spreadsheet structure. In this case I have
>> > several
>> > bills of material where I want to list all the assemblies, but not the
>> > purchased parts. It seems it should be possible, but I'm not having
>> > any
>> > luck
>> > with the filter method.
>> >
>> > Thanks,
>> >
>> > Dave
>>
>>
>>

Thanks. I'll try it.

Dave

"Beege" wrote:

> Dave
> Is there a separate column for the make/buy indicator? You'd autofilter on
> that one. Select the column, Data/Filter Autofilter. Or you could try
> messing around with advanced filter, but I don't use that much
>
> Beege
>
> "Dave" <(E-Mail Removed)> wrote in message
> news:497B0DBA-8D82-420B-9784-(E-Mail Removed)...
> > Beege,
> >
> > Yeah, I tried the autofilter, but maybe (probably) I'm not familiar enough
> > with it. When I filter, how can I specify I just want to see the mfd
> > parts
> > without destroying the BOM structure?
> >
> > "Beege" wrote:
> >
> >> Dave,
> >>
> >> Have you tried using the autofilter for the purchased field?
> >>
> >> Beege
> >>
> >> "Dave" <(E-Mail Removed)> wrote in message
> >> news:FA1162F7-29CD-4F64-9AA7-(E-Mail Removed)...
> >> > I'd like to delete rows from a spreadsheet based on the value of a cell
> >> > in
> >> > a
> >> > row. This is a very large spreadsheet (60,000+ lines). I want to
> >> > delete
> >> > rows based on a value (in this case all parts which are purchased- vs
> >> > mfd)
> >> > without destroying the spreadsheet structure. In this case I have
> >> > several
> >> > bills of material where I want to list all the assemblies, but not the
> >> > purchased parts. It seems it should be possible, but I'm not having
> >> > any
> >> > luck
> >> > with the filter method.
> >> >
> >> > Thanks,
> >> >
> >> > Dave
> >>
> >>
> >>
>
>
>